copy no match
 

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

copy no match
 

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

copy no match
 

works well. thanks.

ankur wrote:
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