Re: Compare two sheets, then copy/paste if different

Tech-Archive recommends: Speed Up your PC by fixing your registry

If column A is formatted as text, you could use the InStr function to test
the other *** for a match and if no match then copy the data over.

for expl
Set wks1 = Sheets("Analysis of Interest Current")
Set wks2 = Sheets("Analysis-Sheet")
For Each c In wks1.Range("A2:A100")
If InStr(wks2.Range("A2:A500"), "7252-0000") = 0 Then
'copy/paste the data
End If
Next

The ranges are arbitrary but should give you an idea of the method.

"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:27537706-9704-4B69-9D6B-0713B120B33F@xxxxxxxxxxxxxxxx
A short time ago I was getting help with a macro that created a new ***,
and named it 'Analysis-***'. Then, I copied everything from a ***
named
'Analysis of Interest Prior' to the 'Analysis-***'. The code is below:


Dim EntireRange As Long
'Delete the *** "Summary-***" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Analysis-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a work*** with the name "Summary-***"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Analysis"

Dim ws1 As Work***, ws2 As Work***
Dim lngRow As Long, lngFRow As Long

Set ws1 = Worksheets("Analysis of Interest Prior")
Set ws2 = Worksheets("Analysis of Interest Current")

ws1.Copy After:=ws2
Set ws1 = Active***
Active***.Name = "Analysis-***"

ws1.Columns("F:J").Clear

Jacob Skaria helped mw with some of the code above.

Now, I am trying to compare the values in Column A of 'Analysis-***'
(these values came from 'Analysis of Interest Prior') with the values in
Column A of 'Analysis of Interest Current'. Then, I want to copy past the
entire row from 'Analysis of Interest Current' if the value in Column A is
not in Column A of 'Analysis of Interest Prior'. The purpose of this is
that
some new accounts are added to 'Analysis of Interest Current' from time to
time, and I want to be able to pick up these new accounts in my analysis.
For instance, I have account number 7250-0000 in both 'Analysis of
Interest
Prior' and 'Analysis of Interest Current', also, I have account number
7252-0000 in both 'Analysis of Interest Prior' and 'Analysis of Interest
Current'. However, I account number 7254-0000 and account number
7255-0000
only appear in 'Analysis of Interest Current', so I'd like to take copy
all
contents from both rows from 'Analysis of Interest Current' and place it
under the end of the list of account numbers in 'Analysis-***'. How can
I
do this?

Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


.

Quantcast