VBA Compare 2 sheets and output difference to 3rd sheet

Closed
jmah - Updated on Oct 17, 2019 at 12:06 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 22, 2019 at 11:43 AM
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
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 17, 2019 at 12:04 PM
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
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 22, 2019 at 11:43 AM
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
0