searching column "A" then Moving Right

[Tompy]

I am looking for a Macro that will...

Starting in "sheet1" take a varying number from a fixed cell referenc
("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, the
paste the contents of cells (A1:A5) that were copied from ("sheet1")

Can anyone help with this?

Regards,

Marcu

 

[R.VENKATARAMAN]

some MVP may give more elegant solution. meanwhile
assume you have in sheet2 some data to the right of the cell having value of
sheet1 F6 try this code

Public Sub test()
Sheet2.Activate
With Columns("a:a")

Cells.Find(what:=Sheet1.Range("F6")).Activate
End With

Sheet1.Range("a1:A5").Copy
ActiveCell.End(xlToRight).Offset(0, 1).PasteSpecial
Application.CutCopyMode = False
End Sub
============================================

 

[Tom Ogilvy]

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