Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to all pivot tables in a workbook
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! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to all pivot tables in a workbook
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 -- HTH... Jim Thomlinson "klysell" wrote: 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! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to all pivot tables in a workbook
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. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "klysell" wrote: 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! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to all pivot tables in a workbook
Pardon my grammar.. It's been a long day.
-- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "klysell" wrote: 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. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "klysell" wrote: 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! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing all pivot tables in workbook | Excel Programming | |||
Help referring to query tables by name | Excel Programming | |||
Copying Pivot Tables from one workbook to another | Excel Worksheet Functions | |||
Refresh all pivot tables in a workbook | Excel Discussion (Misc queries) | |||
Pivot Tables referring to external data query | Excel Discussion (Misc queries) |