Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

linking page variables in two pivot tables

Posted by Barry Borman on April 29, 2001 5:37 AM
I have two pivot tables from the same database on a sheet with the same field (eg, gender) as the page
variable. I what to be able to change the page variable on the first pivot table to, say, 'males',
and get the page variable on the second pivot table to automatically change to 'males'?
Can you help - I assume VBA code is required?


Re: linking page variables in two pivot tables

Posted by Dave Hawley on April 29, 2001 7:54 PM


Hi Barry

Try this untested modified code I have. Assign it to a shortcut key and run it once you have changed the PageField "Gender" on either Pivot Table.

Sub ChangeAllPivotHeaders()
Dim SPgField As String
Dim pPt As PivotTable
Dim wWsht As Worksheet

'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Will change the Page Field on 2 or more Pivot tables
'''''''''''''''''''''''''''''''''''''''''''

On Error GoTo NoPivot
Set pPt = ActiveSheet.PivotTables(1)
SPgField = Pt.PageFields("Gender").CurrentPage
Set pPt = Nothing

On Error Resume Next
For Each wWsht In ThisWorkbook.Worksheets
For Each pPt In wWsht.PivotTables
pPt.PageFields("Gender").CurrentPage = SPgField
Next pPt
Next wWsht

NoPivot:
Set pPt = Nothing
End Sub

Dave

OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.