Jim Thomlinson wrote:
Post one of your sets of 15 lines so that we can see what you are up to...
--
HTH...

Jim Thomlinson


"craig" wrote:

I an new to Excel macros, and have inherited a macro that needs to be
simplified. The macro filters a worksheet using a store number as the
unique identifier, copies the filtered data, and saves the new
worksheet with a name that includes that store number. The problem is
that the code for each store contains 15 lines, and when we add a new
store we add 15 more lines. I would like to be able to manage a store
list that the macro would call, and loop through the store list.



Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim strControlDate

'strControlDate = InputBox("Please enter control date for dated
files:", "Control Date Entry", "")

Workbooks.Open Filename:="P:\DPR\GBG PHONE LIST\GBG PHONE LIST
TEMPLATE.xls"
Windows("GBG PHONE LIST MACRO.xls").Activate
strControlDate = Worksheets("MACROS").Range("O17").Value
Sheets("PHONE").Select
Selection.AutoFilter Field:=1, Criteria1:="330"
Range("a2:g65000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows("GBG PHONE LIST TEMPLATE.xls").Activate
Sheets("PHONE").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Sheets("PHONE").Select
ActiveWorkbook.SaveAs Filename:="P:\Club330\" & strControlDate &
".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWindow.Close