Pivot Table Filter vba: Choose one, the other 2 change to match

[pete234]

All I want is for one my Report Filter to change the other 2 pivots when I change one on the same sheet.
I found some code on another site which has all sorts of looping for multiple pages.

I think I just need something simple to change 3 pivot tables.
I also just need Active sheet.

How can I adopt this for my one sheet of 3 pivots?
Or an easier way?

Change All Pivot Tables With One Selection « Contextures Blog

 

[Domenic]

Right-click the sheet tab for the sheet containing your pivot tables, select 'View Code', and place the following code in the sheet module...

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim ptMain As PivotTable
    Dim pfMain As PivotField
    Dim piMain As PivotItem
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim bMI As Boolean
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set ptMain = Target
    
    On Error Resume Next
    For Each pfMain In ptMain.PageFields
        bMI = pfMain.EnableMultiplePageItems
        For Each pt In Me.PivotTables
            If pt.Name <> ptMain.Name Then
                pt.ManualUpdate = True
                With pt.PivotFields(pfMain.Name)
                    .ClearAllFilters
                    Select Case bMI
                        Case False
                            .CurrentPage = pfMain.CurrentPage.Value
                        Case True
                            .EnableMultiplePageItems = bMI
                            For Each piMain In pfMain.PivotItems
                                .PivotItems(piMain.Name).Visible = piMain.Visible
                            Next piMain
                    End Select
                End With
                pt.ManualUpdate = False
            End If
        Next pt
    Next pfMain
    On Error GoTo 0
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[pete234]

My workbook crashes when I try to add or edit this code.
It complains of a missng END SUB @ Application.EnableEvents = True
But it will not allow me to edit it although I can delete and start over but maybe it needs a tweak.


Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 14.0.6106.5005
Application Timestamp: 4e26f27d
Fault Module Name: VBE7.DLL
Fault Module Version: 7.0.16.19
Fault Module Timestamp: 4d430aec
Exception Code: c0000005

 

[Domenic]

It looks like you may not have copied the complete code. Try copying the code again, and this time make sure that the complete code is copied. I've tested the code and it seems fine. Does this help?

 

[pete234]

I tried multiple times but I could no longer edit the workbook without crashing.
I resaved as non-macro workbook which allows me to use now.
However, I want to make sure:
This code should work with 3 pivot tables referencing the same data correct?
Is there a conflict if the value field in the middle-table is referenced in a lookup formula?
Maybe that is the issue?

 

[Domenic]

I tried multiple times but I could no longer edit the workbook without crashing.

As the article you referenced in your original post states, the code requires Excel 2007 or Excel 2010. Can you confirm which version of Excel you're using?

I resaved as non-macro workbook which allows me to use now.

Do you mean that you've removed the code from your workbook, or that you've saved it as an .xlsx file? If you're using Excel 2007 or Excel 2010, you would need to save your workbook as an .xlsm file.

This code should work with 3 pivot tables referencing the same data correct?

Correct.

Is there a conflict if the value field in the middle-table is referenced in a lookup formula?

It shouldn't be an issue.

 

[pete234]

Thanks for your patience I had to resave to get rid of the code to avoid losing work.
Now I entered the code back in and resaved as xlsm and it works except it does not filter the 3rd Pivot table.
Now I found out the last "label" on the pivot3 did not match the others so I fixed that and it works.

 

[Domenic]

That's great. Glad you've sorted it out.

Cheers!

 

[predzer]

Right-click the sheet tab for the sheet containing your pivot tables, select 'View Code', and place the following code in the sheet module...

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim ptMain As PivotTable
    Dim pfMain As PivotField
    Dim piMain As PivotItem
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim bMI As Boolean
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set ptMain = Target
    
    On Error Resume Next
    For Each pfMain In ptMain.PageFields
        bMI = pfMain.EnableMultiplePageItems
        For Each pt In Me.PivotTables
            If pt.Name <> ptMain.Name Then
                pt.ManualUpdate = True
                With pt.PivotFields(pfMain.Name)
                    .ClearAllFilters
                    Select Case bMI
                        Case False
                            .CurrentPage = pfMain.CurrentPage.Value
                        Case True
                            .EnableMultiplePageItems = bMI
                            For Each piMain In pfMain.PivotItems
                                .PivotItems(piMain.Name).Visible = piMain.Visible
                            Next piMain
                    End Select
                End With
                pt.ManualUpdate = False
            End If
        Next pt
    Next pfMain
    On Error GoTo 0
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This is Awesome! But this code works only if the pivot tables are in same sheet.

Can you please suggest to sync pivot tables in multiple sheets as well?

 

[Domenic]

This is Awesome! But this code works only if the pivot tables are in same sheet.

Can you please suggest to sync pivot tables in multiple sheets as well?

Have a look at the following link...

https://contexturesblog.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/