MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Tables


Posted by Darren Donegan on April 18, 2001 12:55 AM

I'm using Excel 97. I have two queries.
1. Is it possible to create several pivot tables from the same data, and update all of them by selectin the "page tab" just once ?

2. Can field totals be represented in multiple formats in the same table ie as % of total and as normal ?


Posted by Dave Hawley on April 18, 2001 2:15 AM


Hi Darren

Here is some code I wrote for another person wanting change all page fields to be the same.


Here is some VBA code that will change ALL pivot Tables in the Workbook to have the same Page Fields as the one you change.

To put in the code, Push Alt+F11, then go to Insert>Module and paste in this:


Sub ChangeAllPivotHeaders()
Dim SPgField As String, SPgField2 As String
'Written by OzGrid Business Applications
'www.ozgrid.com
SPgField1 = Selection
SPgField2 = Selection.Offset(1, 0)
Dim Pt As PivotTable
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
For Each Pt In Wsht.PivotTables
Pt.PageFields(1).CurrentPage = SPgField1
Pt.PageFields(2).CurrentPage = SPgField2
Next Pt
Next Wsht

End Sub


Now push Alt+Q and save.

Push Alt+F8 and select "ChangeAllPivotHeaders", then click "Options" and assign a shortcut key.

Now select the TOP current Page Field Item in any Pivot Table and click the drop down arrow and change it to whatever. With the same cell still selected (TOP current Page Field Item)click the drop arrow for the second Page Field and change it's page field item to whatever. Now push your shortcut key.


Dave


OzGrid Business Applications

Posted by Mark W. on April 18, 2001 6:39 AM

> Can field totals be represented in multiple formats in the same table ie as % of total and as normal ?

Yes