The following VBA macro will create a list of all the slicers in the active workbook, and the sheet that the slicer is on. The list is printed in the Immediate window of the VB Editor (Ctrl+G).
This code could be modified to add the list to a range in a worksheet.
Sub List_Slicers()
'Description: List all slicers and sheet names in the Immediate window
'Author: Jon Acampora, Excel Campus
'Source: https://www.excelcampus.com/vba/vba-macro-list-all-slicers/
Dim sc As SlicerCache
Dim sl As Slicer
For Each sc In ActiveWorkbook.SlicerCaches
For Each sl In sc.Slicers
Debug.Print sl.Caption & " | " & sl.Parent.Name
'sl.Caption = slicer header caption
'sl.Parent.Name = worksheet name
Next sl
Next sc
End Sub
Explanation of the VBA Code
The macro above loops through all the slicer caches in the workbook. The slicer cache contains a collection of one or more slicers.
The number of slicers in the cache depends on the connections to the pivot tables. Slicers that are connected to the same pivot tables will share the same slicer cache. Checkout this Office Blog article for more details on how the slicer cache works.
The macro then loops through each slicer in the slicer cache, and prints the slicer's caption and parent worksheet name to the Immediate Window.
sl.Caption is the code to return the slicer's header caption. You could also use sl.Name to return the slicer's name, or sl.SourceName to return the source name.
Hi Jon,
Hope you are well! I have a data base and used pivot table and slicer. I frequently add and remove some cases/rows in the database. However, I can still see “Blank” option in the slicer tab when I remove some cases. Could you please help me to write a macro to remove “Blank” from the slicer.
Thanks,
Abdus
Hi,
great job 😉
I’m looking for any method/property for listing slicers’ pivot tables linked to. I can’t see anything proper in object browser – could you help me?
Thanks
Wojtek
Hi Wojtek,
Great question! Here is an article by my friend Jan Karel that helps explain the relationships between pivot table and slicers in VBA. The pivot table is a member of the slicer cache, and the pivot tables can be listed by looping the pivot tables in the slicer cache. I hope that helps. Thanks!
Thanks,
in fact I have done it on my own, getting slicercache instead of slicer. But I don’t know what is the difference between slicer and slicercache objects – could you explain it?
I have also another problem – while changing pivottable.sourcedata property all links between this table and slicers vanish.
I’m working on a macro, which will change all pivot tables’ source data in certain file (many hidden pivot tables). The algorithm is as follows: create table with all slicers’ names, links and pivot tables’ parent names, then remove links between slicer and table, change source data and then re-create links. But it can’t re-create links, because slicers can’t see pivot tables.
Tried the code, been looking for something like this as I have an Excel with many pivots and many slicers which I control using VBA.
Unfortunately the code does not work for me. It doesn’t appear to do anything.
Sorry,
Jonathan
Hi Jon,
This code runs on the active workbook, so you will want to make sure the workbook that contains the pivots is active before running the macro. The output in on the Immediate Window. Press Ctrl+G in the VB Editor to see the Immediate Window. I hope that helps.
Hello Jon Acampora
I have a question about pivot table & slicer , when I click to slicer table and sorted data , can I show the filtered table from the original in the same worksheet of pivot ?
This is GREAT code and a time-saver as I need to generate the values clicked by user for EACH slicer… and i have HUNDREDS of slicers… however…
in your code I only see about 1/3 – 1/2 of my slicer titles displayed in the immediate window… is there a way to put a pause in the code after, say, the first 50, then I can snag those, resume running and grab the next 50, etc?
Thank you for posting this! Very helpful!
This code seems to get the slicer CAPTION, not the slicer NAME. How can it be modified to get the name?
Actually to be more specific, the problem I’m getting is that Debug.Print sl.SourceName doesn’t seem to work for me (Excel 365). It produces Run-time error ‘438’ Object doesn’t support this property or method