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