Loop through visible Slicers on Active Sheet

[JimMatthews]

Hi

I want to write a macro that gets the name of all the slicers on the active sheet. I have written this code, and that seems to loop through all the slicers in the book, not the sheet. I want to just find the ones on the active sheet.

Sub FindSlicers()
    Dim mySlicer As SlicerCache
    
    For Each mySlicer In ActiveWorkbook.SlicerCaches
        x = mySlicer.Name
    Next mySlicer
    
End Sub

Any help appreciated

 

[Andrew Poulsom]

Try:

Sub FindSlicers()
    Dim myCache As SlicerCache
    Dim mySlicer As Slicer
    For Each myCache In ActiveWorkbook.SlicerCaches
        For Each mySlicer In myCache.Slicers
            If mySlicer.Shape.TopLeftCell.Worksheet.Name = ActiveSheet.Name Then
            MsgBox mySlicer.Name
            End If
        Next mySlicer
    Next myCache
End Sub

 

[JimMatthews]

Thanks. Exactly what I was after.

 

[mikeymay]

I am using this and it works to a degree....

I have 6 slicers in the active sheet, but the above is only returning 5 of them.

Is there a setting on each of the slicers that allows it to be visible or not?


Apologies, I didn't allow the code to complete and the 6th wasn't 'next' to the initial 5 it found!!

Works perfectly!!!