Dim rng as Range, rng1 as Range, res as Variant
res = Application.Match(Worksheets("Sheet1").Range("F6") , _
Worksheets("Sheet2").Columns(1),0)
if not iserror(res) then
set rng = Worksheets("Sheet2").Range("A1:A65536")(res)
set rng1 = rng.Parent.Cells(rng.row,"IV").end(xltoLeft)(1,2)
Worksheets("Sheet1").Range("A1:A5").Copy
rng1.PasteSpecial paste:=xlValues, Transpose:=True
End if

--
Regards,
Tom Ogilvy


"Tompy" wrote in message
...

I am looking for a Macro that will...

Starting in "sheet1" take a varying number from a fixed cell reference
("F6")

Copy cells ("A1:A5") - in sheet1

search for the contents of ("F6") in ("Sheet2") column ("A:A")

Move right to the first empty cell at the end of the relevent row, then
paste the contents of cells (A1:A5) that were copied from ("sheet1")

Can anyone help with this?

Regards,

Marcus


--
Tompy
------------------------------------------------------------------------
Tompy's Profile:
http://www.excelforum.com/member.php...o&userid=14732
View this thread: http://www.excelforum.com/showthread...hreadid=276920