Problems using a macro to operate multiple pivot tables

Question

I have a large spreadhseet with 8 pivot tables all using the same filters so would make life easier to choose the filter on one pivot and al lthe others follow.  I found this link to an example sheets that seems perfect  http://www.contextures.com/excelfiles.html#Pivot.   PT0025.  I have asusmed if I just copy all the macro code into my own workbook and change the names of the pivotfields to my own it would just work.  However it doesn't do anything. I don't see anything in the code that looks workbook specific. This is the code I have now.  I'm not sure how this macro is getting called as in the exaple workbook it just owrks when it is opened. Am I missing a step to activate the macro?  I'm also not sure what the test2,3 & 4 macros are doing as they are not caleld in the test() macro. 

Sub test4()

    ActiveSheet.PivotTables(1).PivotFields("code Source").EnableMultiplePageItems = False

    ActiveSheet.PivotTables(1).PivotFields("Gender").EnableMultiplePageItems = False

End Sub

Sub test3()

    ActiveSheet.PivotTables(1).PivotFields("code Source").EnableMultiplePageItems = True

    ActiveSheet.PivotTables(1).PivotFields("Gender").EnableMultiplePageItems = True

End Sub

Sub test2()

    ActiveSheet.PivotTables(1).PivotFields("code Source").CurrentPage = "(All)"

    ActiveSheet.PivotTables(1).PivotFields("Gender").CurrentPage = "(All)"

End Sub

Sub test()

On Error Resume Next

Dim ws As Worksheet

Dim pt As PivotTable

Dim pf As PivotField

Dim pi As PivotItem

Dim bMI As Boolean

On Error Resume Next

'bMI = True

'bMI = False

Application.EnableEvents = False

Application.ScreenUpdating = False

'    For Each ws In ThisWorkbook.Worksheets

    Set ws = ActiveSheet

        For Each pt In ws.PivotTables

            For Each pf In pt.PageFields

            Debug.Print pf.Name

            Debug.Print pf.EnableMultiplePageItems

                If pf.EnableMultiplePageItems = True Then

                    pf.CurrentPage = "(All)"

                End If

                'pf.CurrentPage = "(All)"

                'pf.EnableMultiplePageItems = bMI

            Next pf

        Next pt

'    Next ws

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

thanks for any advice

Answer 1

You need to look in the worksheets' codemodules for this code (below), which is present for each sheet containing a pivot table.  The Test macros are just Tests - they are not actually used in controlling the pivot tables, but were probably used to help develop the working code.

Bernie

PS - there is a note on the first sheet "To see the code for the Worksheet_PivotTableUpdate event, right-click the sheet tab, and choose View Code."

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

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

Answer 2

ah ok so i need ot copy this code into each individual page that has a pivot table i want affected. Thoguht i was missing something critical! wil ltry that thanks alot