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

Question

Hi, 

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!

TrowaD · Accepted Answer

Hi,

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!

TrowaD · Answer

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.

TrowaD · Answer

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.

TrowaD · Answer

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

Comparing Two Excel Sheets and copying like data to third sheet

4 responses

Similar discussions

Subscribe To Our Newsletter!