searching column "A" then Moving Right
 

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

--
Tomp
-----------------------------------------------------------------------
Tompy's Profile: http://www.excelforum.com/member.php...fo&userid=1473
View this thread: http://www.excelforum.com/showthread.php?threadid=27692

searching column "A" then Moving Right
 

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
============================================

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

searching column "A" then Moving Right
 

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