Comparing data between sheets, and copying rows with data
 

I have a list of data on one worksheet, I want to compare the values in this
list of data with values in another column of a different worksheet. When
there is a match between these values I would like to copy the row from the
second worksheet to a completely different worksheet. I have seen some
examples of finding data, and copying data, but do not really have a full
enough understanding of the principles to make this work. In a nutshell I
want to look at sheet 1 column A, compare these values with sheet 2 column M
and if there is a match found copy the row that contains the match from sheet
2 to sheet 3. Can this be done?
Thank you for any assistance you can provide.

Comparing data between sheets, and copying rows with data
 

Sub copyData()
dim rng1 as Range, rng2 as Range
Dim cell as Range, rw as Long
Dim sh as Worksheet
With worksheets("Sheet1")
set rng1 = .Range(.Cells(1,"A"),.Cells(.rows.count,"A").End(x lup))
End With
With worksheets("Sheet2")
set rng2 = .Range(.Cells(1,"M"),.Cells(.rows.count,"M").End(x lup))
End With
set sh = Worksheets("Sheet3")
rw = 1
for each cell in rng2
if application.countif(rng1,cell) 0 then
cell.EntireRow.copy sh.Cells(rw,"A")
rw = rw + 1
end if
Next
End sub

--
Regards,
Tom Ogilvy

"Fleone" wrote:

I have a list of data on one worksheet, I want to compare the values in this
list of data with values in another column of a different worksheet. When
there is a match between these values I would like to copy the row from the
second worksheet to a completely different worksheet. I have seen some
examples of finding data, and copying data, but do not really have a full
enough understanding of the principles to make this work. In a nutshell I
want to look at sheet 1 column A, compare these values with sheet 2 column M
and if there is a match found copy the row that contains the match from sheet
2 to sheet 3. Can this be done?
Thank you for any assistance you can provide.