Mike,Per and Marcus - many thanks to you all for your efforts, I have managed
to get this to work so again thanks
:)

"Rick Rothstein (MVP - VB)" wrote:

Does this do what you want?

Sub TransferData()
Const StartCol As String = "A"
Const EndCol As String = "O"
Const SourceSheet As String = "Sheet1"
Const SourceRow As Long = 2
Const DestSheet As String = "Sheet2"
Const DestStartRow As Long = 12
Dim DestEndRow As Long
Dim X As Long
Dim Y As Long
With Worksheets(DestSheet)
DestEndRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
For X = DestStartRow To DestEndRow
If .Cells(X, StartCol).Value = Worksheets(SourceSheet). _
Cells(SourceRow, StartCol).Value Then
For Y = Asc(StartCol) - 64 To Asc(EndCol) - 64
Worksheets(SourceSheet).Cells(SourceRow, Y).Copy .Cells(X, Y)
.Cells(X, Y).Value = .Cells(X, Y).Value
Next
End If
Next
End With
End Sub

Rick


"Anthony" wrote in message
...
Many thanks to you all for ur efforts,
Just one 'problem'
when the data is pasted into the correct row in sheet2 the A cell some how
is populatated with a formula =SHEET1!A12

any reason why this is doing so as it returns the value 0 (zero)
thanks

"marcus" wrote:

Hi Anthony

This will replace all found instances of sheet1 A2, in Sheet 2 in the
range given.

Sub FindRplce()

Set rngA = Sheets("Sheet1").Range("A2")
Set rngB = Sheets("Sheet2").Range("A12:A100")
Set CpyRng = Sheets("Sheet1").Range("A2:O2")

For Each CellB In rngB
Found = False
For Each cellA In rngA
If CellB.Value = cellA.Value Then
Found = True
CpyRng.Copy CellB
End If
Next
Next
End Sub

Regards

Marcus