Finding and pasting over a row

B

Big Chris

Hi, hope someone might be able to help me.....

I have data in rows on sheet 1 where column A is the unique referenc
and am pulling a single row of that data into sheet2 via VLOOKUP an
altering some of it.
I then want to copy the entire row in sheet2, and find the location o
the original row in sheet1 based on the unique reference and paste th
new values over the top of that row.

Make sense? It's obviously part of a much larger project, but I'
kinda stuck at this stage. Your help would be greatly appriciated.

Regards,

Chris
 
T

Tom Ogilvy

Dim rng1 as Range, rng2 as Range
Dim cell1 as Range, cell2 as Range
Dim res as Variant
' set source range
With worksheets("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
' set range on sheet2
With worksheets("Sheet2")
set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

for each cell2 in rng2
res = application.Match(cell2.value,rng1,0)
if not iserror(res) then
set cell1 = rng1(res)
cell2.entireRow.Copy
cell1.Pastespecial xlValues
end if
Next
 
Top