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.
______________________
Hello, and many thanks for the help on the code.
However I need your help on the below:
I have a master filter were I do my choices, then I have a sheet with several pivot that will automaticaly update when the master filter is selected. On the 3rd sheet, I also have several pivot, but on this sheet I need the general information, so I need to “exclude the code” for this sheets
How can I do it?
Thanks in advanced
Frederico, you can add a couple of lines to check the sheet name:
For Each ws In ThisWorkbook.Worksheets If ws.Name <> "MySheetName" Then '==new line 1 For Each pt In ws.PivotTables 'all the existing code here Next pt End If '==new line 2 Next wsHi all
Anyone having success using this brilliant functionality having Data Cubes as the source for the pivot tables ?
Best Regards
Lasse
Lasse: Can’t you just connect a slicer up to each of the PivotTables concerned? Or are they based on completely different data sources?
Hi,
This is a great bit of code. I was wondering if there is any enhancement to this whereby a pivot table returns a blank if the selection doesn’t appear ? I am selecting an individual person in my main pivot that exists in a number of other pivots, except one. This pivot returns all the data and I wanted it to return nothing (blank). Is this possible?
Thanks,
Chris
Hi,
Is there an easy way to update slicers on the subsequent tabs based on the selections from the first tab? I have duplicate slicers on each tab, which do not get updated as the page field filters are updated. I have numerous worksheets, all with pivots, but I want my users to be able to use the slicer on each tab (all different data sources, but with all common slicers).
Thanks
Bill
Bill: Take a look at my answer at this StackOverflow thread:
https://stackoverflow.com/questions/39599449/vba-to-connect-slicers-looking-for-improvements-to-code
Hi All,
What an amazing code, thanks you!
I’m a VBA rookie, wanted to know what should I do in order to apply this code only on a certain field?
In Debra’s example, i want to make all the pivot tables to change according to the employee field (that won’t happen now and I’m wondering why)
Hi Debra,
Thank you for the great codes and lessons. They have saved me in several instances.
I am trying to filter multiple items in 2 pivot tables in different tabs using the macro in ,”PT0025 – Change All Page Fields with Multiple Selection Settings”. The filter names are same for both pivot tables which the data sources are different. There are common items among multiple items to select in the drop down as well as different items ( though the filter name is same).
When I apply multiple filters to 1st pivot table , the 2nd pivot table picks selected items in pivot table 1 filter and some other non-listed items. At times the 2nd tables picks entirely different items, if the selected item in pivot 1 is not available in pivot 2.
Your help is highly appreciated.