Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chg 1 "Last, First Mid" column to 3 "First", "Middle", "Last" colu | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
Moving "sort by column" | Excel Discussion (Misc queries) | |||
Moving between "row" and "range" formats in VBA | Excel Programming |