Referring to all pivot tables in a workbook

[Guest]

Hi,

I have many worksheets each with three pivot tables residing on them. I
would like call up formatting procedures to apply identical formatting to
each group of three pivot tables without applying the same formatting to
worksheets that don't contain pivot tables. The first 5 sheets in my workbook
don't contain pivot tables while the 6th and beyond all do. Here is the code
that I have so far:

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
Call Format_Grouping_Titles
Call Format_Columns_RC
Call Format_ThinLines_5
Call Format_ThinLines_6
Call Format_ThinLines_7
Call Format_ThinLines_9
Call Format_Titles_1
Call Anamolies
Call Home
Next pt
Next ws

What am I doing wrong? This code only applies the pivot table formatting sub
procedures to the Summary sheet which doesn't even have a pivot table. How
about starting the formatting procedures on the worksheet starting after the
Summary sheet and applying this formatting to all worksheets that follow? The
Summary sheet is the 5th sheet in my workbook, so I'd like to start this
formatting on the 6th sheet and beyond. Any pointers?

Thanks in advance!

 

[Guest]

While you are traversing all of the sheets and all of the pivot tables in the
sheets you are not passing the reference to the pivot table you find to the
formatting procedures. Your formatting procedures need to be modified to
accept a pivot table to be formatted.

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
Call Format_Grouping_Titles(pt)
Call Format_Columns_RC(pt)
Call Format_ThinLines_5(pt)
Call Format_ThinLines_6(pt)
Call Format_ThinLines_7(pt)
Call Format_ThinLines_9(pt)
Call Format_Titles_1(pt)
Call Anamolies(pt)
Call Home(pt)
Next pt
Next ws

 

[Guest]

Hi Jim,

Thanks for your reply. I implemented your code and it's still working, but
only on the Summary sheet and not the worksheets after which contain pivot
table. Is it possible to select all worksheets after the Summary sheet and
then apply the formatting as a group?

Here is what my code looks like now:

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
Call formatting(pt)
Next pt
Next ws


The argument is passed through ok, but the action is still only done on the
Summary sheet. What you have any idea what it's not recognizing the correct
worksheets that contain pivot tables?

Thanks very much!
Kent.

 

[Guest]

Pardon my grammar.. It's been a long day.