How to prevent filtering in pivots, if a pivotField is not among report filters?

[Macao]

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