Macro to automatically change all pivot table dates if i change one - half working

Question

This is my first time using stack overflow and I have very limited knowledge of macros. For some reason the macro below only works properly if I select a single date. If i try to select multiple items on one pivot table, it then just updates the rest of the pivot tables by selecting every option in the filter dropdown.

Example of code:

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

I want this code to run automatically when a date is changed in a pivot table on a particular sheet, either by selecting a single date or multiple, I have about 10 pivot tables on this sheet (unfortunately they all have their own data source and do not point to the same set of data).

When I select one single date, say the 01/01/2022 (with select multiple items unchecked) it will update all the other pivot tables accordingly. When I select one single date with "select multiple items" checked, it will select every date/option from the dropdown filter.

Any ideas what changes can be done to have the macro run and select multiple dates on all the other pivot tables? I need to be able to select either a single date or a date range such as a whole month

Thank you for taking the time to read :)

pgSystemTester · Answer

I'm not sure what all of your code is doing with the filters and what not, but here's a basic macro that refreshes all pivotables in the workbook (thisWorkBook). Make sure this is placed in a Module, not the worksheet tab.

Sub refreshAllPV()
   Dim p As PivotTable, ws As Worksheet, wkbk As Workbook
   
   Set wkbk = ThisWorkbook 'better to set this a parameter when calling

      For Each ws In wkbk.Worksheets
         For Each p In ws.PivotTables
            p.PivotCache.Refresh
         Next p
   Next ws

End Sub

From here, you just need to capture the proper event. When I run this test code, it fires on both multiple and single filter changes. You might want to condition the action to only launch the macro if a particular pivot table is modified.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   Const nNameOfDisiredPivot = "PivotTable1" 'or whatever it is. Make sure case is accurate
   
   If Target.Name = nNameOfDisiredPivot Then
      MsgBox "Macro called for change to: " & Target.Name 'included just for testing to see when event is called.
      Call refreshAllPV
   End If
   
End Sub

Macro to automatically change all pivot table dates if i change one - half working

1 Answers