Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a macro universal to create multiple worksheets
I recorded a macro but was unable to accomplish what I was hoping for. I am trying to pull a store number from a list I have. Copy and paste the store number into a specific cell in another sheet then save the file using the store number as the filename. Then I will need to sort a separate worksheet using that same store number and copy and paste the first 12 customers first and last names. I will need to repeat this process 2 other times from separate worksheets but only pull 5 and then 3 customers from those worksheets. Then save and close. Unfortunately when I tried this it recorded 10026 which was the first store number. As I mentioned I would like for it to be easily useable so that I can do this for some 200 separate store creating 200 separate worksheets. I have attached to code from the macro record. Any assistance would be greatly appreciated. Thanks, so much, Bob Range("F1").Select ActiveCell.FormulaR1C1 = "10026" Range("F2").Select ChDir "C:\Documents and Settings\kbobba\Desktop\2006 offsite LTS" ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\kbobba\Desktop\2006 offsite LTS\10026 offsite audit.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Windows("off-site list 3-24-06.xls").Activate Selection.AutoFilter Field:=3, Criteria1:="=10026", Operator:=xlAnd Range("A2:L2").Select Selection.Copy Windows("10026 offsite audit.xls").Activate Range("H1").Select ActiveSheet.Paste Range("F1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "10026" Range("F2").Select Windows("RALs 3-24-06.xls").Activate Sheets("Ral sheet1").Select Selection.AutoFilter Field:=4, Criteria1:="=10026", Operator:=xlAnd Range("B1840:C10068").Select Selection.Copy Windows("10026 offsite audit.xls").Activate Range("E6").Select ActiveSheet.Paste ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 7 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 14 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 7 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 1 Range("F1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "10026" Range("F2").Select Windows("RALs 3-24-06.xls").Activate Sheets("Declines").Select Selection.AutoFilter Field:=4, Criteria1:="=10026", Operator:=xlAnd Range("B2913:C6154").Select Selection.Copy Windows("10026 offsite audit.xls").Activate Range("E20").Select ActiveSheet.Paste ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Range("F1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "10026" Windows("RALs 3-24-06.xls").Activate Sheets("ERCSheet1").Select Range("D4").Select Selection.AutoFilter Field:=4, Criteria1:="=10026", Operator:=xlAnd Range("B308:C1116").Select Selection.Copy Windows("10026 offsite audit.xls").Activate Range("E31").Select ActiveSheet.Paste ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 24 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 26 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 34 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 36 ActiveWindow.ScrollRow = 37 ActiveWindow.ScrollRow = 39 ActiveWindow.ScrollRow = 38 ActiveWindow.ScrollRow = 36 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 16 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- bobbak ------------------------------------------------------------------------ bobbak's Profile: http://www.excelforum.com/member.php...o&userid=22495 View this thread: http://www.excelforum.com/showthread...hreadid=527098 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making changes automatically on multiple worksheets | Excel Discussion (Misc queries) | |||
VBA Excel Macro - One File Auto Create Multiple Excel Worksheets! | Excel Programming | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) | |||
Macro for making worksheets visible. | Excel Programming | |||
How to I create a macro in Excell to add multiple worksheets? | Excel Programming |