It is telling me that it finds 0 matches. I made sure to put some in there
that do match the reference data worksheet. After I click the box to verify
there were 0 records found another box informing me of:

Runtime error '13"
Type mismatch

Thanks for your assistance,

"Simon Lloyd" wrote:


Thats because Excel thinks the sheet doesn't exist, it may be that there
is an extra space in the name in the code or your worksheet tab or it's
mis spelled.

Kcope8302;423534 Wrote:
When I press CRT+A(shortcut key) and try to run this macro I get the
following error.

I get a Run-Time error '9':
Subscript out of range.

It then highlites the following row.
Set Rng = Sheets("Work Area").Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)



"Simon Lloyd" wrote:


This should do what you need:

Code:
--------------------
Sub copy_data()
Dim Rng As Range, Rng1 As Range, MyCell As Range, oCell As Range, i
As Long
Set Rng = Sheets("Work Area").Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Reference Data").Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
i = 0
For Each MyCell In Rng1
For Each oCell In Rng
If oCell.Value = MyCell.Value Then
oCell.EntireRow.Copy Destination:=Sheets("PTR").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
i = i + 1
End If
Next oCell
Next MyCell
MsgBox "There were " & i & " items copied to PTR", vbInformation,
"Record Count"
i = ""
End Sub
--------------------





--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117763