copy no match

[vernalGreens]

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

 

[ankur]

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

 

[vernalGreens]

works well. thanks.

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