How to List All Slicers in the Excel Workbook with VBA

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).

List All Slicers in the Excel Workbook with VBA - Print to the Immediate Window

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

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.

Slicer Settings Window VBA Slicer Property References

Related Articles


Your email address will not be published. Required fields are marked *

  • 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.


  • 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?

    • 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.


    • 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

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter