| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Macro
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify Macro
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to modify my macro ... | Excel Discussion (Misc queries) | |||
| Modify A Macro | Excel Worksheet Functions | |||
| Modify a Macro | Excel Worksheet Functions | |||
| Modify a macro | Excel Programming | |||
| Help to modify macro please | Excel Programming | |||
Linear Mode
