Clear Slicers Using VBA

[Tanner_2004]

I use this code all the time, thanks to one of the forum members!

Sub SlicerReset()

Dim slcr As SlicerCache
Application.ScreenUpdating = False
   For Each slcr In ActiveWorkbook.SlicerCaches
    
        slcr.ClearManualFilter
    Next slcr
Application.ScreenUpdating = True


End Sub

However, I would like to confine it to just the active worksheet or a specific worksheet. Is that possible? Thank you for reading.

[Aflatoon]

Yes and no. If the slicer is linked to tables on another sheet, it will obviously affect those sheets too.

Try this:

Sub SlicerReset()

    Dim slcr                  As SlicerCache
    Dim slc                   As Slicer
    
    Application.ScreenUpdating = False
    
    For Each slcr In ActiveWorkbook.SlicerCaches
        For Each slc In slcr.Slicers
            If slc.Shape.Parent Is ActiveSheet Then
                slcr.ClearManualFilter
                Exit For
            End If
        Next slc
    Next slcr
    
    Application.ScreenUpdating = True




End Sub

[Tanner_2004]

Yes and no. If the slicer is linked to tables on another sheet, it will obviously affect those sheets too.

Try this:

Sub SlicerReset()

    Dim slcr                  As SlicerCache
    Dim slc                   As Slicer
    
    Application.ScreenUpdating = False
    
    For Each slcr In ActiveWorkbook.SlicerCaches
        For Each slc In slcr.Slicers
            If slc.Shape.Parent Is ActiveSheet Then
                slcr.ClearManualFilter
                Exit For
            End If
        Next slc
    Next slcr
    
    Application.ScreenUpdating = True




End Sub

Thank you. I am looking forward to giving this a try. When I send my dashboard/workbook to others, I delete the worksheet that is storing the source data to shrink the size of the file. Therefore, the pivot table, charts, etc rely on the pivot cache. Will this code allow me to continue the same practice? Thank you again.

[Aflatoon]

As long as your slicers still work manually when you do that, the code should be fine.

[Tanner_2004]

Thank you! I will try and let you know.