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
'Source: https://www.excelcampus.com/library/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.

Slicer Settings Window VBA Slicer Property References

Related Articles

8 comments

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

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

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

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

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

  • 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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly