PowerPivot Slicer Not Refreshing after VBA Filter

Question

I am trying to use a macro to filter a pivot table using the below code snippet. The code works, however the Slicer does not update to reflect the filter that was just applied. If I manually filter the pivot table, the slice does update.

I tried refresh/refresh all, manual update settings, and screen update settings. I am stuck. Any ideas?

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[DataWithDuplicates_FULL].[PA_Select].[PA_Select]"). _
   PivotFilters.Add Type:=xlCaptionContains, Value1:=SrchTrm

Brad Brad · Accepted Answer · 2015-08-14T20:22:39

The slicer works by looking at whether or not the PivotItem is visible or not. Using the PivotFilters.Add Type:=xlCaptionContains kind of strategy will appropriately filter your data but will not "check" the right filter boxes.

The way to get filters/slicers to respond in the way you want is like

Dim pf  As PivotField
Dim pi As PivotItem
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("[DataWithDuplicates_FULL].[PA_Select].[PA_Select]")
pf.ClearAllFilters
For Each pi In pf.PivotItems
    pi.Visible = pi.Caption Like "*" & SrchTrm & "*"
Next pi

Careful about selecting no values because the pivot table will throw an error when you tell it to select nothing.

PowerPivot Slicer Not Refreshing after VBA Filter

1 Answers