Change page field in multiple pivot tables on one sheet

bgarth · May 7th 2004

I have 3 or more pivot tables on one sheet and would like the page field of all to change with the first one. There is a couple sources of code around these groups (Dave Hawley, 4/29/01), but I cannot seem to get them to work. Does anyone have any suggestions?

yjoshi · May 27th 2004

Interesting question ...

I will do it this way, instead of changing it in page, I change the value in a cell, say E1

and then change all the page values.... by this code....

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("E1")).Address = Range("E1").Address Then
For Each pvt In ActiveSheet.PivotTables
        pvt.PivotFields("City").CurrentPage = Range("E1").Value
Next
End If
End Sub

It is working for me... .let me know if it does not.... I will attach a file.

HTH

skip_borland · Nov 16th 2010

Re: Change page field in multiple pivot tables on one sheet

Thanks for the code sample, HTH

What if you need to change multiple values (i.e. selecting multiple items within the page field to be hidden or not)? My table is not based on an OLAP cube and it seems like there are all kinds of limitations if it is not.

I would also be open to simply changing the page field to a row field temporarily, making the selections and returning it to a page field. But I can't find examples of how to do that either.

Thoughts?

AustralVBA · Sep 15th 2012

Re: Change page field in multiple pivot tables on one sheet

If I understand correctly:

Select an item in any pivot table's page fields, and page fields for pivot tables on the other worksheets will change to the same Item.
Matching page fields are also set to the same setting for "Select Multiple Items"

Code

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


Dim wsMain As Worksheet
Dim ws As Worksheet
Dim wsPTF As Worksheet
Dim ptMain As PivotTable
Dim ptF As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pfPTF As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
Dim bPTF As Boolean


On Error Resume Next


Set wsMain = ActiveSheet


If wsMain.Name <> Me.Name Then GoTo exitHandler


Set wsPTF = Sheets("PT_Fields")
Set ptMain = Target
Set ptF = wsPTF.PivotTables("PT_List")


Application.EnableEvents = False
Application.ScreenUpdating = False


For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
bPTF = False
For Each pfPTF In ptF.PageFields
If pfMain.Name = pfPTF.Name Then
bPTF = True
Exit For
End If
Next pfPTF
If bPTF = False Then
Exit For
End If
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False


Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain


exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Could not update all pivot tables"
Resume exitHandler
End Sub

Display More

Change page field in multiple pivot tables on one sheet

Participate now!

Share