Refresh ALL Pivot Slicers on ActiveSheet only

[JonesyUK]

Dear All,

I am using the following to refresh ALL Pivot Slicers in ALL worksheets of my Workbook:

Sub Slicer()
Dim slcr As SlicerCache
For Each slcr In ActiveWorkbook.SlicerCaches
slcr.ClearManualFilter
Next slcr
Call Refresh
End Sub

Question: how do I get this to work on ONLY the Active Sheet??? i.e. I don't want it to run on all the pivots in the entire Workbook...

Many thanks,
JonesyUK

 

[Andrew Poulsom]

What do you mean by refresh a pivot slicer? The Refresh method applies to a PivotCache object not a SlicerCache object. What is your Refresh procedure do?

 

[JonesyUK]

I have 5 "Slicers" from my Pivot Table. The user can make a number of selections from these handy menus... All I am trying to do is have a button on the ActiveSheet which, when clicked, will "uncheck" all the Slicers that have selections.

Hope this makes sense... the code above works, but runs on all sheets in the workbook... i only need it to run on the active sheet....

Thanks,

 

[Andrew Poulsom]

You can use code like this to return the name of the worksheet that contains the Slicers for a given SlicerCache

MsgBox ActiveWorkbook.SlicerCaches(1).Slicers(1).Parent.Name

Here's another way using the Slicer's Shape property:

PowerPivot VBA Macro Mania! « PowerPivotPro