Previous Topic Next Topic

Compare sheet 1 and 2 and copy to sheet 3

Closed
Pat - Mar 19, 2015 at 09:45 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 21, 2015 at 07:00 AM
Hi,

I got this code which compare sheet 1 and 2 and copy to sheet 3 data with similar data from 1 and 2. Now what i need is to cell from sheet 2 to sheet 3 what has no similar record in sheet 1. Like below for sample but i also need to copy the whole row.

Sheet 1
column a column b column c
dog cat mouse
moose dear antelope


Sheet 2
column a column b column c
bear coala something
dog cat mouse

Sheet 3
column a column b column c
bear coala something


Thanks.
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 21, 2015 at 07:00 AM
download data from
http://speedy.sh/HxJrA/pat-150321.xlsm
see column D in sheet1 and sheet 2
copy D1 in each sheet down.

try this macro (which is already in the vb editor of this file)

macro repeated here for reference purposes 


Sub test()
Dim r As Range, c As Range, cfind As Range
With Worksheets("sheet3")
.Cells.Clear
End With
With Worksheets("sheet2")
Set r = Range(.Range("C1"), .Range("C1").End(xlDown))
For Each c In r
With Worksheets("sheet1").Columns("C:C")
Set cfind = .Cells.Find(what:=c, lookat:=xlWhole)
If Not cfind Is Nothing Then
c.EntireRow.Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
End With
Next c
End With
With Worksheets("sheet3")
.Range("D1").EntireColumn.Delete
End With
End Sub
0