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?
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
