Hi Vernal,
Try this...
Sub test()
On Error Resume Next
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim cell As Object
Set S1 = Sheets("Sheet1")
Set S2 = Sheets("Sheet2")
For Each cell In S2.Range("B1:B" & S2.Range("B65536").End(xlUp).Row)
RowInS2 = 0
RowInS2 = S1.Columns("b:b").Find(what:=cell.Value,
lookat:=xlWhole).Row
If RowInS2 = 0 Then
S2.Rows(cell.Row).Copy
S1.Select
Range("C65536").End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste
S2.Select
End If
Next cell
Application.CutCopyMode = False
End Sub
Regards
Ankur Kanchan
www.xlmacros.com
wrote:
sheet1
column A column B
R1
R2 ABC 10
R3 DEF 12
R4 LMN
sheet2
column A column B
R1
R2 ABC 9
R3 XYZ 13
R4 DEF 11
R5 PQR 15
I've to find out all the column A values of sheet2 that are not in
column A of sheet1 (in this example XYZ and PQR) and paste the entire
row of sheet 2 in the next null row under column B of sheet1. Since
null row under column B of sheet 1 is R4, R4 will be overwritten.
Thus the output will look like
sheet1
column A column B
R1
R2 ABC 10
R3 DEF 12
R4 XYZ 13
R5 PQR 15