Change All Pivot Tables With One Selection

from contextures.com

Change All Pivot Tables With One Selection:

Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.

Change All Pivot Table Filters

Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.

For example, 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, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and 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 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 Excel 2010, if you enable macros.

Watch the Excel Video Tutorial

To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.

Or watch on YouTube: Change All Pivot Tables When One Changes

______________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Change All Pivot Tables With One Selection

Please visit the Contextures page on Facebook