VBA Compare 2 sheets and output difference to 3rd sheet

-
Hello,

I am trying to make a button on VBA that will take compare two excel sheets, and identify new numbers in a certain column and copy and paste those rows onto a new sheet. So basically in Sheet 1, there are numbers in column H. If in Sheet 2 there is a new number in column H that was not listed in column H on Sheet 1, then the whole row will be copied and pasted into Sheet 3. Any help would be appreciated!

System Configuration: Windows / Chrome 77.0.3865.120
See more 

1 reply

Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370
0
Thank you
Hi Jmah,

The following code will do as requested:
Sub RunMe()
Dim mFind As Range

For Each cell In Sheets("Sheet2").Range("H1", Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp))
    Set mFind = Sheets("Sheet1").Columns("H").Find(cell.Value)
    If mFind Is Nothing Then
        cell.EntireRow.Copy Sheets("Sheet3").Range("H" & Rows.Count).End(xlUp).Offset(1, -7)
    End If
Next cell

End Sub


Best regards,
Trowa
Thanks,
It works perfectly. Is there anyway you can make it so it only copies and pastes the values that are over 20,000 in row R. Thanks.

-Jmah
TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370 -
Hi Jmah,

Sure, for that we need to change the 6th code line:
    If mFind Is Nothing Then

into:
    If mFind Is Nothing And Sheets("Sheet2").Range("R" & cell.Row).Value > 20000 Then


Best regards,
Trowa
Respond to TrowaD