Compare two sheets and paste in new sheet

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I need some help for my work.

Sheet1 contains some numbers in column A2:A500

Sheet 2 contains some numbers in A2 : AXXX

I need a macro which checks the values in column A of sheet 1 with the values in Column A in sheet 2 and copy the values which are not found in Sheet 2 to a new sheet.

Please help me on this .


Sheet 2 is a subset of sheet 1. sheet 1 will be updated on a regular basis. every time sheet 1 is updated this macro should comapre the values in sheet2 and should paste the new values which are not found in sheet 2 to a new sheet .

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
try this macro (modify to suit you)

Sub test()
Dim r As Range, c As Range, cfind As Range, x As Variant
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
x = c.Value
With Worksheets("sheet2").Columns("A:A")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = cfind
End With 'sheet3
End If
End With 'shee2
Next c
End With 'sheet1

End Sub