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 |
All times are GMT +1. The time now is 09:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com