Comparing Two Excel Sheets and copying like data to third sheet [Solved/Closed]

coz2992 1 Posts Monday June 9, 2014Registration date June 9, 2014 Last seen - Jun 9, 2014 at 12:16 PM - Latest reply: TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen
- Apr 28, 2015 at 11:39 AM
I'm trying to compare two sheets in excel and have matching column data copy the entire row into a new third sheet. Ex:

Compare

Sheet 1 column C to Sheet 2 column E, if the numbers match copy the entire row of matching sheet 1 column C data to a third Sheet 3.

Not sure if this is an "If, then" or "Match" function.

Any help would be appreciated

Thanks!
See more 

9 replies

Best answer
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jun 10, 2014 at 12:13 PM
21
Thank you
Hi Coz,

So you want to move an entire row of column C data? Haha. I will go with the entire row, ok?

Assuming column A from sheet1 contains data, here is the code:
Sub RunMe()
Dim lRow, x As Long

Sheets("Sheet1").Select
lRow = Range("C1").End(xlDown).Row

For Each cell In Range("C2:C" & lRow)
    x = 2
    Do
        If cell.Value = Sheets("Sheet2").Cells(x, "E").Value Then
            cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
        x = x + 1
    Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "E"))
Next
    
End Sub


To implement code:
In Excel hit Alt+F11 to open Microsoft Visual Basic window. Go to top menu Insert > Module. Now paste the code in the big white field. You can now close Microsoft Visual Basic window.

To run the code:
Back at excel hit Alt+F8 and double-click RunMe.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Thank you, TrowaD 21

Something to say? Add comment

CCM has helped 1706 users this month

The script above to compare two excel sheets and copy like data to a third sheet works great. What would be the opposite formula? Two compare two excel sheets and copy unlike data to a separate sheet?
I got it ...

Sub RunMe()
Dim lRow, x As Long

Sheets("Sheet1").Select
lRow = Range("C1").End(xlDown).Row

For Each cell In Range("C2:C" & lRow)
x = 2
Do
If cell.Value = Sheets("Sheet2").Cells(x, "E").Value Then
Exit For
End If
x = x + 1
Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "E"))
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next

End Sub
Cancel that ... it did not work. Thoughts?
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Apr 23, 2015 at 11:23 AM
2
Thank you
Hi MistaWizard,

That's because it should be xlValues instead of just Values.

So change the code line into:
Set fValue = .Find(cell.Value, LookIn:=xlValues)

Sorry for the confusion.
Changed this in the Feb 5 code.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
MistaWizard 2 Posts Thursday April 23, 2015Registration date April 23, 2015 Last seen - Apr 24, 2015 at 02:53 AM
Thanks TrowaD :) This worked, almost.

It only copied one row, instead of the hundreds I know there are. I'm going to keep playing with the code (my VB is pretty awful tho), and will update this if I get it right.
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Feb 5, 2015 at 11:01 AM
0
Thank you
Hi Pikno,

OK ...

Great ...

aaahhhh ...

Take a look and try the following code:
Sub RunMe()
Dim lRow, lrow2 As Long
Dim fValue As Range

Sheets("Sheet1").Select
lRow = Range("C1").End(xlDown).Row
lrow2 = Sheets("Sheet2").Range("E1").End(xlDown).Row

For Each cell In Range("C2:C" & lRow)
    With Sheets("Sheet2").Range("E2:E" & lrow2)
        Set fValue = .Find(cell.Value, LookIn:=xlValues)
        If fValue Is Nothing Then
            cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next cell
    
End Sub


Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
MistaWizard 2 Posts Thursday April 23, 2015Registration date April 23, 2015 Last seen - Apr 23, 2015 at 03:37 AM
Hi TrowaD,

I'm getting runtime error 9 subscript out of range on the line
Set fValue = .Find(cell.Value, LookIn:=Values)
Any suggestions?
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Apr 28, 2015 at 11:39 AM
0
Thank you
Hi MistaWizard,

Could this be the issue?:
If you don't have data in column A of Sheet1, then the copied row will be overwritten. This is because of the 13th code line:
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

The code will look at the first available row in column A of Sheet3. So when a row is pasted, but column A is still empty, then the next row being pasted will be in the same row, thus overwriting the previous one.

A tip figuring out what the code does when, is to place your cursor on the code and then hit F8. This will run a code line each time you hit F8.

Good luck and have fun!

Best regards,
Trowa