Mass Pivot Table Changes

[Lee_J08]

Hi All,

I am brand new to working with VBA and have pretty much no knowledge of VBA so It would be fantastic if someone could help me solve my issue.

I am working on a excel sheet that has 100+ pivot tables across all sheets (large database). One filter element remains the same within all pivot tables and that is the "Period" which refers to the time periods the data displays in the Pivot Tables. At this point in time I found some code online that allows me to change all the Pivot Tables in the sheet but I need to be able to to direct it at only certain Pivot Tables so that they can be compared against other data. On top of this I also need Pivot Tables other data to not be filtered, just the Period filed should change.

Option Explicit
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
 
'change all fields for all pivot tables on active sheet
 
For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
        For Each pt In wsMain.PivotTables
            If pt <> 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 pfMain
   
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

If someone has any tips on how to do this it would make my life a hell of a lot easier!
Many Thanks,
Lee

[SamT]

Rez Bump