Excel: Filter Pivot Table from Another Sheet / Duplicate Filters Across Sheets

Question

I am working with Excel for Mac 2011.

I have several different pivot tables that each organize data for graphing on a summary sheet. Every pivot table should be filtered the same way.

Questions:

(1) Is there any way to move/duplicate/link these filters so that when one changes the others change as well? (VBA works for single selections now, but what about multiple selections?)

(2) Is there any way to create a single, universal filter on the main summary page?

---

EDIT: Basically, I'm looking to emulate SLICERS in Excel for Mac. Any ideas?

Answer 1

Using some examples that I found online, I wrote the following VBA that accomplishes what I need. I placed an empty pivot table on my summary page, and a button linked to this macro that updates all other pivot tables with the same filters.

---

DISCLAIMER: This will not work for fields that have "(All)" Selected. It wasn't necessary for me to look into, but it's possible to modify the following code to include "(All)" support.

---

Sub UpdateAllPivotTables()
Dim ws As Worksheet
Dim wsMain As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim piMain As PivotItem
Dim pi As PivotItem
Dim pf As PivotField
Dim hadError As Boolean


On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = ActiveSheet.PivotTables(1)

Application.EnableEvents = False


For Each ws In ThisWorkbook.Worksheets
        If (Not (ws.Name = wsMain.Name)) Then
            ws.Unprotect
            For Each pt In ws.PivotTables
                    pt.ManualUpdate = True
                    For Each pf In pt.PageFields
                            For Each pi In pf.PivotItems
                                If (Not (pi.Visible = ptMain.PageFields(pf.Name).PivotItems(pi.Name).Visible)) Then
                                    pi.Visible = ptMain.PageFields(pf.Name).PivotItems(pi.Name).Visible
                                End If
                            Next pi
                    Next pf
                    pt.ManualUpdate = False
            Next pt
            'ws.Protect
        End If
Next ws
Application.EnableEvents = True

End Sub