There is a new sample file on the Contextures website, with a macro to change all pivot tables with one selection, when you change a report filter in one pivot table.
Change All Pivot Tables
In the sample workbook, if you change the “Item” Report Filter in one pivot table, all the other pivot tables with an “Item” filter will change.
They get the same report filter settings that were in the pivot table that you changed.
Select Multiple Items
In this version of the sample file, the “Select Multiple Items” setting is also changed, to match the setting that is in the pivot table that you changed.
In the screen shot below, the Item field has the “Select Multiple Items” setting turned off. If any other pivot tables in the workbook have an “Items” filter, the “Select Multiple Items” setting for those fields will also change.
How It Works
The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.
For each report filter field, the code checks for the Select Multiple Items setting, to change all Pivot Tables with the same report filter field.
The code loops through all the worksheets in the workbook, and loops through each pivot table on each sheet.
Private Sub Worksheet_PivotTableUpdate _
(ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
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
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Download the Sample File
To test the Change All Pivot Tables code, you can download the sample file from the Contextures website.
On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 – Change All Page Fields with Multiple Selection Settings.
The file will work in Excel 2007 or later, if you enable macros.
Watch the Video
To see the steps for copying the code into your worksheet, and an explanation of how the code works, watch this short video.
______________________
@IAN – Try the new code at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ which should be even faster.
@All – suggest you also update the code with this latest version. Note that the workbook contains two code modules, and you need BOTH of them.
Hi there! Love this – quick question for you though… (Excel 2007) will this work to update all of a specific filter that is date related? Here’s my situation – I have a huge dataset. I have multiple pivot tables on multiple sheets to help me provide multiple reports,etc. One constant that I have is that I update all pivots to have the field called “WkEnding” as Greater than . For example, this week it was Greater Than 3/31/13. SO… ON some tables it is a column filter, on others it is a row filter.
Will this work to update all of these?
Thank you!! You are awesome!
I think so. Don’t see why not. Give it a try, and post back if you have any issues.
Let me change up the question real quick, because I had to set the report aside for a while & i am just now jumping back into it….
On a particular worksheet within the workbook I have a bunch of pivot tables. The column header is “Wk Ending”. This is the item that I need to filter. I re-pulled code from here and tried it just now. When I update the “Wk Ending” field all of the other pivot tables completely lose the filter. They show all dates. Here is the code I am using. Can you help me out? Thank you so much!!
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
‘change only Region field for all pivot tables on active sheet
Set pfMain = ptMain.PivotFields(“Wk Ending”)
bMI = pfMain.EnableMultiplePageItems
For Each pt In wsMain.PivotTables
If pt ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(“Wk Ending”)
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
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Hi, How do i update the code to make 1 specific pivot as a master pivot? thanks!
So you only want the code to sync tables if a particular pivottable is changed?
yes, this is exactly what I want to do