Making a macro universal to create multiple worksheets

B

bobbak

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro that deletes and pastes filterd rows and then some 2
Macro Error 6
Macro code 0
macro to copy rows that contain a cell with a certain number 3
Need Help with VBA code for an Excel Model Macro 5
Macro Saving 1
Macro Help 0
Macro to hide rows with a certain cell empty then set print area and print 12

Top