VBA Clear all Slicers and refresh Pivot table Excel 2013

Question

I am looking for VBA to Clear all Slicers and refresh Pivot table Excel 2013, however, it breaks on the "ClearAllFilters". Everything I research suggest using it. is there a better way to refresh slicers and display all results. Also, would like to include the refreshing of the Data model, and pivot tables.

Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)

Dim sc As SlicerCache
Dim scs As SlicerCaches
Dim slice As Slicer

Set scs = ws.Parent.SlicerCaches

If Not scs Is Nothing Then
    For Each sc In scs
        For Each slice In sc.slicers
            If slice.Shape.Parent Is ws Then
                slice.ClearAllFilters
                Exit For 'unnecessary to check the other slicers of the slicer cache
            End If
        Next slice
    Next sc
End If

End Sub

I tried your suggeston, however, still having issue with the ClearAllFilters.

    Sub Reset()
Dim pt As PivotTable

 Application.ScreenUpdating = False
 ActiveWorkbook.Model.Refresh
    RefreshSlicersOnWorksheet ActiveSheet
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
Application.ScreenUpdating = True
End Sub
Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)
Dim pt As PivotTable
Dim slice As Slicer

For Each pt In ws.PivotTables
    For Each slice In pt.Slicers
        slice.SlicerCache.ClearAllFilters
    Next slice
    pt.PivotCache.Refresh
Next pt
End Sub

barrowc barrowc · Accepted Answer · 2016-03-10T22:57:35

The ClearAllFilters method applies to the SlicerCache object and not to the Slicer object

Change:

slice.ClearAllFilters

to:

sc.ClearAllFilters

edit: alternatively you could start with the PivotTables collection from the Worksheet and access the slicers from there. You could then refresh the pivot table, like this:

Dim pt As PivotTable
Dim slice As Slicer

For Each pt In ws.PivotTables
    For Each slice In pt.Slicers
        slice.SlicerCache.ClearAllFilters
    Next slice
    pt.PivotCache.Refresh
Next pt

VBA Clear all Slicers and refresh Pivot table Excel 2013

1 Answers