Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Macro to compare 2 sheets and copy differences [Solved/Closed]

Report
Posts
1
Registration date
Monday February 3, 2020
Status
Member
Last seen
February 3, 2020
 -
Posts
1
Registration date
Saturday February 15, 2020
Status
Member
Last seen
February 17, 2020
 -
Hello, I’ve been working with this macro to compare two worksheets and copy the matches to a third sheet. Not knowing vb very well can someone show me how to modify this to only copy what is not found?

Sheet1 and Sheet2 column A is the unique id.

If no match is found between Sheet1 and Sheet2 then copy the row in Sheet1 to Sheet3

This works below to copy the matches but I’m looking for what is not found now. 

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)

If cfind Is Nothing Then GoTo line1

c.EntireRow.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

End With 'sheet 2
line1:
Next c
Application.CutCopyMode = False
End With 'sheet 1


End Sub
Related:

2 replies

Answer 1 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Hi Ehusar,

For that you only have to add NOT to code line 12: 
If not cfind Is Nothing Then GoTo line1


Best regards,
Trowa
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 2
Posts
1
Registration date
Saturday February 15, 2020
Status
Member
Last seen
February 17, 2020
Great idea man thanks keep it up all the time. i am very happy to see your standard.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!