Previous Topic Next Topic

VBA Compare 2 sheets and output difference to 3rd sheet

Closed
jmah - 16 Oct 2019 à 19:26
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 22 Oct 2019 à 11:43
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

1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
17 Oct 2019 à 12:04
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
jmah
21 Oct 2019 à 19:19
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 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
22 Oct 2019 à 11:43
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

Similar discussions

Macro to compare 2 sheets and copy differences
ehusar -
AirMaple -

2 responses
How to compare two Excel sheets with varying data
BWIRyan -
TrowaD -

15 responses
Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
macro to compare two excel sheets
annajins -
Aks -

6 responses
Comparing two excel worksheets
squirrel -
lcouri -

10 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response