PDA

View Full Version : Mass Pivot Table Changes



Lee_J08
09-20-2013, 07:30 AM
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
09-24-2013, 05:30 PM
Rez Bump