Excel 2007 Filtering across multiple Pivot Tables

Question

In Excel 2007, I have a single dataset in the form of an Excel Table from which I want to make multiple pivot tables and pivot charts. I would like to be able to synchronize the 'report filter' field across all of the pivot tables and charts simultaneously, so that by filtering, (for example) the US_Region field to 'Pacific Northwest' on one of the pivot tables would apply that filter to all of the pivot tables and charts in the workbook (which share a single dataset). Is there a way to do this without VBA and, if there is not, what would be the most flexible/least klugy way to do it in VBA?

Thanks

Answer 1

Create a procedure to change all the page fields. This one loops through all the worksheets in the workbook and if the worksheet has a pivot table, it changes the page field to match the page field on the pivot table passed to the sub.

Sub ChangePage(pt As PivotTable)

    Dim strPageValue As String
    Dim wks As Worksheet
    Dim ptUpdate As PivotTable

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    strPageValue = pt.PivotFields("Area").CurrentPage

    For Each wks In ThisWorkbook.Worksheets

        On Error Resume Next
        Set ptUpdate = wks.PivotTables(1)

        If Err.Number = 0 Then
            On Error GoTo 0
            wks.PivotTables(1).PivotFields("Area").ClearAllFilters
            wks.PivotTables(1).PivotFields("Area").CurrentPage = strPageValue
        End If
        Err.Clear

    Next wks

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Then place a call to this procedure in the worksheet code itself and pass the pivot table you changed:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call ChangePage(Target)

End Sub

Change the name of the pivot field from "Area" to the page field you need, for example "US_Region".

This may not be suitable if people are frequently changing the structure of your pivot tables in other ways, as it will trigger each time the pivot table is changed, including when it is refreshed. It will also give an error if users remove the specified page field.

If the pivot tables are static though, this might do.