Clear FIlters of Pivot Table after Cell Value Changes

[thatfarooq]

I wrote this code to change different pivot filters (region level, subregional level, MBU level, and FID level) when the cell value changes. The cell value changes when I use the slicers on another sheet. How to ensure that the filter is reset (cleared) once the cell value changes? I have mentioned "xPField.ClearAllFilters" in each case but it does not seem to work, any solutions?

Explanation:
Based on the value in Cell B2, I want to update the pivot table filters (on any of the four filter levels). I am using PivotTableUpdate event, because it updates the PivotField of the PivotTable on the sheet based on cell value B2.

Value in Cell B2 changes whenever I click on the slicer. It is not directly connected with the slicer but is based on the slicer value. It changes such that if I select an MBU from the slicer, it lists down all the connected MBUs in that subregion for comparison. (Screenshot attached)

Suppose I select the MBU "N1-RWP-03", it'll list down all MBUs from N1 (North 1) in the list in range "B2:B10". Now based on the value in B2, which would be "N1-RWP-01", I want the Pivot Table "RegionBackend" to apply a filter on the Pivot Field "MBU" because it is on the MBU level. The pivot table change event does that, Now, I cleared the slicer and applied a slicer on North 3, B2:B10 would show all subregions, and a filter is also applied on "North 1" subregion in the pivot table (since it is in B2), but the previous filter on the "MBU" pivot field is not cleared. I want it to be cleared.

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim xPTable As PivotTable
    Dim xPField As PivotField
    Dim xValue As String
   
    Set xPTable = Worksheets("Calc").PivotTables("RegionBackend")
    xValue = Worksheets("Calc").Range("B2").Value
   
    Select Case xValue
    Case Is = "North":
        Set xPField = xPTable.PivotFields("Region")
        Application.EnableEvents = False
        xPField.ClearAllFilters
        xPField.CurrentPage = xValue
        xPTable.RefreshTable
        Application.EnableEvents = True
    Case Is = "North 1", "North 2", "North 3":
        Set xPField = xPTable.PivotFields("Subregion")
        Application.EnableEvents = False
        xPField.ClearAllFilters
        xPField.CurrentPage = xValue
        xPTable.RefreshTable
        Application.EnableEvents = True
    Case Is = "N3-JLM-08":
        Set xPField = xPTable.PivotFields("MBU")
        Application.EnableEvents = False
        xPField.ClearAllFilters
        xPField.CurrentPage = xValue
        xPTable.RefreshTable
        Application.EnableEvents = True
    Case Is = "6442":
        Set xPField = xPTable.PivotFields("FID")
        Application.EnableEvents = False
        xPField.ClearAllFilters
        xPField.CurrentPage = xValue
        xPTable.RefreshTable
        Application.EnableEvents = True
    End Select
End Sub

 

[thatfarooq]

Soft Reminder
Is it too advanced a concept for everyone in this forum? I thought it would be easier than I thought.

 

[Alex Blakenburg]

I have mentioned "xPField.ClearAllFilters" in each case but it does not seem to work, any solutions?

You are clearing the filter on the field you are applying a new filter to but it sounds like you want to clear all the previous page filters.
If that is the case how about just before your Select Case statement you run this:

    For Each xPField In xPTable.PageFields
        xPField.ClearAllFilters
    Next pf

 

[thatfarooq]

Thank you.

    For Each xPField In xPTable.PageFields
        xPField.ClearAllFilters
    Next pf

It worked. It did the job I wanted: clear all filters after cell value in B2 changes.

I have also cleaned the code, but I am facing a new small problem now. The pivot table is not refreshing after the filter is applied. I have to manually refresh the filter each time to get the desired calculations. Can you check why the pivot table is not refreshing, maybe I'm doing something wrong here:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim xPTable As PivotTable
    Dim xPField As PivotField
    Dim xValue As String
   
    Application.EnableEvents = False
    Set xPTable = Worksheets("Interdependence").PivotTables("CalcFeild1")
    xValue = Worksheets("Interdependence").Range("A2").Value
    
    For Each xPField In xPTable.PageFields
        xPField.ClearAllFilters
    Next xPField
    
    Select Case xValue
    Case Is = "North":      Set xPField = xPTable.PivotFields("Region")
    Case Is = "North 1":    Set xPField = xPTable.PivotFields("Subregion")
    Case Is = "N1-RWP-01", "N2-PSH-01", "N3-MRP-01":    Set xPField = xPTable.PivotFields("MBU")
    Case Is = "6442", "7262", "6016":   Set xPField = xPTable.PivotFields("FID")
    End Select
    xPField.CurrentPage = xValue
    xPTable.RefreshTable
    Application.EnableEvents = True
End Sub

 

[Alex Blakenburg]

There has to be something else going on. Changing the filter should not require a refresh.
The sequence is: Underlying data change, Refresh Table, Filter Change.
So if anything I would move the refresh to immediately after the "Set xPTable = ..."

If that doesn't work then:
What are you seeing that is telling you it needs a refresh ?
Has anything changed in underlying data ?
Are you using Power Query somewhere along the line ?