Hi!
I have several pivot tables in a file, and in some of them a pivotField called "Month number" among the report filters.
I also have a macro which, whenever I modify the "Month number" pivotField in the report filter in any of the pivot tables, sets the other pivot table filters equal as the one I just modified.
My problem is that the macro changes the selection also in pivot tables where the "Month number" pivotField is NOT among the report filters. I only want the macro to change the pivotField selections if that pivotField is among the report filters..
Here is my code:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim booSUBefore As Boolean
booSUBefore = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If (ws.Name = Sh.Name) And (pt.Name = Target.Name) Then
GoTo SamePT
Else
Set pf = Target.PivotFields("Month number")
pt.ManualUpdate = True
pt.PivotFields("Month number").ClearAllFilters
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
If pi.Visible = True Then
pt.PivotFields("Month number").PivotItems(pi.Name).Visible = True
End If
Next pi
Next pf
pt.ManualUpdate = False
End If
SamePT:
Next pt
Next ws
ErrorHandler:
Application.ScreenUpdating = booSUBefore
Application.EnableEvents = True
End Sub
Any suggestions are appreciated!
/Macao
I have several pivot tables in a file, and in some of them a pivotField called "Month number" among the report filters.
I also have a macro which, whenever I modify the "Month number" pivotField in the report filter in any of the pivot tables, sets the other pivot table filters equal as the one I just modified.
My problem is that the macro changes the selection also in pivot tables where the "Month number" pivotField is NOT among the report filters. I only want the macro to change the pivotField selections if that pivotField is among the report filters..
Here is my code:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error GoTo ErrorHandler
Application.EnableEvents = False
Dim booSUBefore As Boolean
booSUBefore = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If (ws.Name = Sh.Name) And (pt.Name = Target.Name) Then
GoTo SamePT
Else
Set pf = Target.PivotFields("Month number")
pt.ManualUpdate = True
pt.PivotFields("Month number").ClearAllFilters
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
If pi.Visible = True Then
pt.PivotFields("Month number").PivotItems(pi.Name).Visible = True
End If
Next pi
Next pf
pt.ManualUpdate = False
End If
SamePT:
Next pt
Next ws
ErrorHandler:
Application.ScreenUpdating = booSUBefore
Application.EnableEvents = True
End Sub
Any suggestions are appreciated!
/Macao