If you use Google groups, click the about group, then under "Top
Posters" click on Tom Oglivy, go through and read his posts, there is
more information there than you will get out of a book anytime soon.

Charles

craig wrote:
Charles,

That works perfect, thank you so much for your help. This should make
my day much less complicated. Do you have any advice on how to learn
this stuff? Books, web sites?


Die_Another_Day wrote:
Try ws2.Range("A1:G65000").AutoFilter Field:= 1,
Criteria1:=ws1.Range("A" & cnt).Value

Charles

craig wrote:
Received a compile error he

ws2.AutoFilter Field:=1, Criteria1:=ws1.Range("A" & cnt).Value


Die_Another_Day wrote:
Give this a try:
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim strControlDate
Dim ws1 As Worksheet 'Worksheet with Store List
Dim ws2 As Worksheet 'Worksheet with Data
Dim wb1 As Workbook 'Template book
Dim lRow As Long 'Last Row
Dim cnt As Long 'Count
Set ws1 = Workbooks("GBG PHONE LIST MACRO.xls").Sheets("Craigs
Worksheet")
Set ws2 = Workbooks("GBG PHONE LIST MACRO.xls").Sheets("Phone")
lRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
For cnt = 2 To lRow 'Assumes Row 1 is header info
strControlDate = Workbooks("GBG PHONE LIST
MACRO.xls").Worksheets("MACROS").Range("O17").Valu e
Set wb1 = Workbooks.Open(Filename:="P:\DPR\GBG PHONE LIST\GBG
PHONE LIST TEMPLATE.xls")
ws2.AutoFilter Field:=1, Criteria1:=ws1.Range("A" & cnt).Value
ws2.Range("A2:G65000").SpecialCells(xlCellTypeVisi ble).Copy
wb1.Sheets("Phone").Range("A2").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
wb1.SaveAs Filename:="P:\Club" & ws1.Range("A" & cnt) & "\" &
strControlDate & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
wb1.Close
Application.CutCopyMode = False
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Charles

craig wrote:
Difficult for me to tell which are variables. Is ws1 'my worksheet
name'

I'm sure that I can play around with it to get it to work.


Die_Another_Day wrote:
Dim ws1 as Worksheet 'Worksheet with Store List
Dim ws2 as Worksheet 'Worksheet with Data
Dim lRow as Long 'Last Row
Dim cnt as Long 'Count
lRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
For cnt = 2 to lRow 'Assumes Row 1 is header info
ws2.Range("A1").AutoFilter Field:= 1, Criteria1:= "=" & ws1.Range("A" &
cnt).Value
Next

That's the basics, let me know if you need help finishing it.

Charles

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.