Ensure that the filter is reset once cell value changes

[thatfarooq]

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

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":
        Set xPField = xPTable.PivotFields("Subregion")
        Application.EnableEvents = False
        xPField.ClearAllFilters
        xPField.CurrentPage = xValue
        xPTable.RefreshTable
        Application.EnableEvents = True
    Case Is = "N-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

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

How to ensure that the filter is reset once I the cell value changes?

First, to fire the event when you modify a cell, it is better to use the Change event.
To clean the filters, use the table:

Replace your code with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPField As PivotField
    Dim xValue As String
   
    If Target.Address = "$B$2" Then
      Set xPTable = Worksheets("Calc").PivotTables("RegionBackend")
      xValue = Worksheets("Calc").Range("B2").Value
      xPTable.ClearAllFilters
     
      Select Case xValue
        Case Is = "North":    Set xPField = xPTable.PivotFields("Region")
        Case Is = "North 1":  Set xPField = xPTable.PivotFields("Subregion")
        Case Is = "N-JLM-08": Set xPField = xPTable.PivotFields("MBU")
        Case Is = "6442":     Set xPField = xPTable.PivotFields("FID")
        Case Else: Exit Sub
      End Select
     
      Application.EnableEvents = False
      xPField.CurrentPage = xValue
      Application.EnableEvents = True
    End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[thatfarooq]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Hi, thank you for the welcome. much appreciated.

First, to fire the event when you modify a cell, it is better to use the Change event.
To clean the filters, use the table:

Replace your code with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPField As PivotField
    Dim xValue As String
  
    If Target.Address = "$B$2" Then
      Set xPTable = Worksheets("Calc").PivotTables("RegionBackend")
      xValue = Worksheets("Calc").Range("B2").Value
      xPTable.ClearAllFilters
    
      Select Case xValue
        Case Is = "North":    Set xPField = xPTable.PivotFields("Region")
        Case Is = "North 1":  Set xPField = xPTable.PivotFields("Subregion")
        Case Is = "N-JLM-08": Set xPField = xPTable.PivotFields("MBU")
        Case Is = "6442":     Set xPField = xPTable.PivotFields("FID")
        Case Else: Exit Sub
      End Select
    
      Application.EnableEvents = False
      xPField.CurrentPage = xValue
      Application.EnableEvents = True
    End If
End Sub

I changed the event to Change, and there were no errors in the code, but it also seemed to do nothing. It did not change the filters of my pivot table, that was the main reason I wrote this code in the first place.

Can you check the code again?

P.S: I'm using a .xlsm file format. If you want, I can upload the file as well, but it is confidential data that I'm working with, and I would not be able to share the data with you.

 

[DanteAmor]

For the change event to be triggered, in this particular case, you must modify the data in cell B2.

According to your code, it is in cell B2 where you have the filter value.

 

[thatfarooq]

Oh, let me explain what I want to do.

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

For the change event to be triggered, in this particular case, you must modify the data in cell 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.