Previous Topic Next Topic

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

Answer 1 / 1
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
jmah
Oct 21, 2019 at 07:19 PM
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

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