Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
Hi,
I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
hi kent
try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
Hi Susan,
Nice to hear from you! Hey, I remember that you wanted a copy of the spreadsheet you were helping me a few months ago. Would you still like a copy? I think I still have your e-mail address saved at home. I'll check out your solution. Thank you kindly, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
Hey Susan,
This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
yes, i don't see why not.......
sub commandbutton1_click() for each sheet in workbook call Parameters next sheet end sub then Parameters would contain whatever formatting and manipulation you wanted to have on each cell. :) yes, you can send the spreadsheet if you like! susan On Aug 3, 11:10 am, klysell wrote: Hey Susan, This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
ok. lol. What if I want to do this formatting only on sheets (names not known
until user enters in code which creates the pivot tables on the sheets) that contain pivot tables and not on the summary, parameters, PIV_Deliverables and PIV_RC sheets? I've been grapling with this one for awhile.... -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: yes, i don't see why not....... sub commandbutton1_click() for each sheet in workbook call Parameters next sheet end sub then Parameters would contain whatever formatting and manipulation you wanted to have on each cell. :) yes, you can send the spreadsheet if you like! susan On Aug 3, 11:10 am, klysell wrote: Hey Susan, This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
This is the code (dims omitted and other code that refreshes the data) that
only enacts the formatting macro on the Summary Sheet and not on the sheets that contain the pivot tables. For Each ws In sh.PivotTables With pt Call Formatting End With Next ws -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: yes, i don't see why not....... sub commandbutton1_click() for each sheet in workbook call Parameters next sheet end sub then Parameters would contain whatever formatting and manipulation you wanted to have on each cell. :) yes, you can send the spreadsheet if you like! susan On Aug 3, 11:10 am, klysell wrote: Hey Susan, This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
yes, big LOL, i've been seeing your repeated reposts asking for help
with this. :D apparently nobody has the time or experience to answer @ the moment. i have no experience with pivot tables. if the user enters name, then you can grab that as a constant (or static) instead of a variable. i'm thinking also that you can grab the ORDER of the sheets instead of the name - i know i've seen that somewhere. of course, you don't know how many sheets there will be.......... & actually, i don't think you need to list them by name. if you use for each ws in wb it won't matter what the name is. oh. i forgot the formatting. oh! ok, you know the names of the sheets that you don't want the formatting on: summary, parameters, PIV_Deliverables, etc. make it an if statement if ws.name = "summary" OR if ws.name = "parameters" OR blah blah blah Then 'do nothing else PUT YOUR FORMATTING CODE HERE - will format all the sheets except the ones you're specifying. end if warning! susan has a very great tendency to write big, bloated, over- kill ideas. :) the guru's can probably do the same thing in 10 lines that which takes me 40. so be forwarned. :) susan, thinking out loud while typing On Aug 3, 2:04 pm, klysell wrote: ok. lol. What if I want to do this formatting only on sheets (names not known until user enters in code which creates the pivot tables on the sheets) that contain pivot tables and not on the summary, parameters, PIV_Deliverables and PIV_RC sheets? I've been grapling with this one for awhile.... -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: yes, i don't see why not....... sub commandbutton1_click() for each sheet in workbook call Parameters next sheet end sub then Parameters would contain whatever formatting and manipulation you wanted to have on each cell. :) yes, you can send the spreadsheet if you like! susan On Aug 3, 11:10 am, klysell wrote: Hey Susan, This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
Susan,
You kill me. Even bigger LOL. Thanks for your ideas... they'll probably will help me. cheers! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: yes, big LOL, i've been seeing your repeated reposts asking for help with this. :D apparently nobody has the time or experience to answer @ the moment. i have no experience with pivot tables. if the user enters name, then you can grab that as a constant (or static) instead of a variable. i'm thinking also that you can grab the ORDER of the sheets instead of the name - i know i've seen that somewhere. of course, you don't know how many sheets there will be.......... & actually, i don't think you need to list them by name. if you use for each ws in wb it won't matter what the name is. oh. i forgot the formatting. oh! ok, you know the names of the sheets that you don't want the formatting on: summary, parameters, PIV_Deliverables, etc. make it an if statement if ws.name = "summary" OR if ws.name = "parameters" OR blah blah blah Then 'do nothing else PUT YOUR FORMATTING CODE HERE - will format all the sheets except the ones you're specifying. end if warning! susan has a very great tendency to write big, bloated, over- kill ideas. :) the guru's can probably do the same thing in 10 lines that which takes me 40. so be forwarned. :) susan, thinking out loud while typing On Aug 3, 2:04 pm, klysell wrote: ok. lol. What if I want to do this formatting only on sheets (names not known until user enters in code which creates the pivot tables on the sheets) that contain pivot tables and not on the summary, parameters, PIV_Deliverables and PIV_RC sheets? I've been grapling with this one for awhile.... -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: yes, i don't see why not....... sub commandbutton1_click() for each sheet in workbook call Parameters next sheet end sub then Parameters would contain whatever formatting and manipulation you wanted to have on each cell. :) yes, you can send the spreadsheet if you like! susan On Aug 3, 11:10 am, klysell wrote: Hey Susan, This works well, but I was wondering if I could enact one macro button on the "Parameters, Macros" sheet that moves through all my worksheets that contain pivot tables and then subsequently refreshes and formats them using my "Formatting" procedure. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Susan" wrote: hi kent try splitting it up - put the actual formatting macro in a regular module, rather than behind a sheet, & have the command button call the 2nd macro. sub commandbutton1_click() Call Format_Stuff end sub public sub Format_Stuff() do your stuff end sub make sure it's a public sub. hope that helps! susan On Aug 3, 9:20 am, klysell wrote: Hi, I have a macro assigned to the CommandButton1 on each worksheet starting on the sixth worksheet and going until the last tab of my workbook. In each instance, the macro does the same procedure, but only acting on the worksheet to which it is attached. How do I move through these worksheets one-at-a-time and enact the macro button? The procedure that is assigned to this button is called "formatting" and deals with formatting my pivot tables. I've tried other more efficient methods, but they seem to only act on my summary sheet and not move through my workbook. Here is the code that I've come up with thus far: Dim pt As PivotTables Dim sh As Worksheet For Each ws In sh.PivotTables With pt Call Formatting(pt) End With Next ws What am I doing wrong? Thanks in advance, Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving through worksheets and enacting formatting macro
well, my daughter always tells me i'm very entertaining.... but i
don't think she means it in a good way! ha ha hope it all works out. susan On Aug 3, 2:54 pm, klysell wrote: Susan, You kill me. Even bigger LOL. Thanks for your ideas... they'll probably will help me. cheers! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 <very big snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving conditional formatting between worksheets | Excel Discussion (Misc queries) | |||
Moving Between worksheets | Excel Programming | |||
moving a row between worksheets | New Users to Excel | |||
Moving between Worksheets | Excel Programming | |||
Moving worksheets | Excel Programming |