Hi

Try this:

Sub Anthony()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim Targetrange As Range

Set sh = Worksheets("Sheet1")
Set sh1 = Worksheets("Sheet2")

With sh1.Range("A12:A100")
Set c = .Find(what:=sh.Range("A2").Value, Lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Ref. # do not exists!")
Exit Sub
End If
End With

sh.Range("A2:O2").Copy Destination:=sh1.Cells(c.Row, 1)
End Sub

Regards,
Per

"Anthony" skrev i meddelelsen
...
In sheet1 cell A2 I have a ref number eg 1234567
I would like a macro to search through cells A12:A100 of sheet2 for the
same
ref number and when found replace the data in that row (columns A:O) with
the
data held in cells A2:O2 of sheet1
any ideas please?
many thanks