Pivot Tables

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

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

Yes