How to List All Slicers in the Excel Workbook with VBA - Excel Campus
7

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
'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

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 7 comments
Mohamed Saber - May 28, 2017

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 ?

Reply
Jon - January 26, 2017

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

Reply
    Jon Acampora - February 4, 2017

    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.

    Reply
Wojtek - August 13, 2016

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

Reply
    Jon Acampora - August 14, 2016

    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!

    Reply
      Wojtek - August 16, 2016

      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.

      Reply
Abdus - June 9, 2016

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

Reply

Leave a Reply: