Moving through worksheets and enacting formatting macro

[Guest]

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.

 

[Susan]

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

 

[Guest]

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.

 

[Guest]

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,

 

[Susan]

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

 

[Guest]

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....

 

[Guest]

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

 

[Susan]

yes, big LOL, i've been seeing your repeated reposts asking for help
with this. 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

 

[Guest]

Susan,

You kill me. Even bigger LOL. Thanks for your ideas... they'll probably will
help me.

cheers!

 

[Susan]

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

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>