PDA

View Full Version : Clear Slicers Using VBA



Tanner_2004
04-19-2016, 09:46 AM
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
04-20-2016, 03:50 AM
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
04-20-2016, 05:03 AM
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
04-20-2016, 05:29 AM
As long as your slicers still work manually when you do that, the code should be fine.

Tanner_2004
04-20-2016, 05:42 AM
Thank you! I will try and let you know.