Anthony,

You don't say if there would be multiple instances on sheet 2 so this will
find them all and paste the data in. Right click sheet 2 sheet tab, view code
and paste this in.

Sub search_Replace()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
If c.Value = Sheets("Sheet1").Range("A2").Value Then
Sheets("Sheet1").Range("A2").Resize(, 15).Copy
c.PasteSpecial
End If
Next
End Sub

If there will only be one instance then add the line
Exit Sub
After the pastespecial line

Mike

"Anthony" wrote:

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