I am trying to create a macro.
I am using a workbook with three separate pages
I need the macro to copy the contents of a single cell - containing a UPC Code -from page 1 it will be in a contineous collum of UPC data
749186214459 => start here (perform routine)
749186057452 <= do this one next and continue to completion
749186202098
749186105252
43538634262
43538743490
43538610754
43538322060
43538049851
43538892211
43538394289
=> End Sub
(This is the required routine once the target cell has the macro executed)
Copy contents -
Switch to another page of the worksheet (page 2)
Search a collum of that sheet and locate the matching target which is the same
UPC copied from (page 1)
Once the target UPC is found
Select the entire row of data that contains that target UPC
switch pages to a third sheet
and paste the row into the next available row
This much would be great.
If it could then return to page 1
select the next UPC code in the collum and repeat the process
until it reaches the end of the collum - that would be perfect.
I have tried repeatedly to create this macro and am getting
frustrated. The code embeds the contents of the last cell
searched and over-writes this information on the next UPC
code in the collum.
Code:
Sub Macro30()
'
' Macro30 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(-14, 0).Range("A1").Select
Sheets("InventoryExport_1-28-2011-14-28").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(0, -9).Columns("A:A").EntireColumn.Select
Selection.Find(What:="749186214459", After:=ActiveCell,
LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Sheet1").Select
End Sub
I know my first problem is this line:
Selection.Find(What:="749186214459", After:=ActiveCell,
LookIn:=
I need to replace the specific contents of the target cell (in this
case "749186214459") with a bolean value or indicate - to
pick-up whatever contents are found associated with the
target cell.
My second problem is this line:
Selection.Find(What:="749186214459", After:=ActiveCell,
Again - the target cell contents become part of the macro and
cause problems when i reuse the macro on the next cell. - It
overwrites the contents with the embedded contents.
Can a macro be programed to select the contents of a cell,
paste it, search for it on another sheet, copy the row it is in,
paste it in a third sheet and return to repeat the process with
the next record?
Please let me know - any guidance is appreciated.
dave@justforsize.com