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

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




(E-Mail Removed) 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

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
>
>
>
>
> (E-Mail Removed) 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