VBA - Loop through each visible slicer item

[Seba Robles]

Hello,

I'm trying to loop through all visible slicer items so that I can then copy the updated pivot table based on that slicer selection.

The code below sort of works, it's just not looping through each item on the slicer one by one.

Instead it selects them all and starts by removing one by one. Let's say there are 5 active slicer items, the code currently starts by showing all 5 items, then it shows 4, then 3, etc.

I want to select the first slicer item alone, then the second slicer item by itself, and so on.

Any help is greatly appreciated!!

Sub SlicerTest()

    Dim slItem As SlicerItem, slDummy As SlicerItem
    Dim slBox As SlicerCache

    Set slBox = ActiveWorkbook.SlicerCaches("Slicer_Owner")

    'loop through each slicer item
    For Each slItem In slBox.SlicerItems

        'show all items to start
        slBox.ClearManualFilter

        'test each item against itself
        For Each slDummy In slBox.SlicerItems

            'if the item equals the item in the first loop, then select it
            'otherwise don't show it (thus showing 1 at a time between the nested loops)
            If slItem.Name = slDummy.Name Then slDummy.Selected = True Else: slDummy.Selected = False

    'copy table
    Range("A5", Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Copy

        Next slDummy

    Next slItem

End Sub

 

[Seba Robles]

I checked the names for both the slicer and pivot table, changed them and made the changes on the code and it still didn't work..

The other ways I'm thinking are;
- to count the number of slicer items with data and then loop from 1 to that number
-to countA the pivot table's range where if it's <= 16 do nothing, else continue with the code (16 being the number of header columns)

 

[Domenic]

I don't know why it's not clearing out the old items for you. Maybe someone else here will be able to shed some light.

In the meantime, you can use the HasData property of the SlicerItem object to check whether there's any data...

If slItem.HasData then

 

[Domenic]

When you step through your code, line-by-line, using the F8 key, and you execute the line that refreshes the pivot cache, doesn't the slicer item disappear from the slicer?

 

[Seba Robles]

When I run the code line by line the slicer does refresh, but all the slicer items stay there selected (the ones with data and no data)

I used the HasData property and the code is now looping through the slicer items with data only, this is the final code I used:

Sub SlicerItemsLoop()

    Dim slItem As SlicerItem, slDummy As SlicerItem
    Dim slBox As SlicerCache

    Set slBox = ActiveWorkbook.SlicerCaches("Slicer_EmailOwner")

    'loop through each slicer item
    For Each slItem In slBox.SlicerItems

        If slItem.HasData Then

        'show all items to start
        slBox.ClearManualFilter

            'test each item against itself
            For Each slDummy In slBox.SlicerItems

                'if the item equals the item in the first loop, then select it
                'otherwise don't show it (thus showing 1 at a time between the nested loops)
                If slItem.Name = slDummy.Name Then slDummy.Selected = True Else: slDummy.Selected = False

            Next slDummy

                'copy table and paste in new sheet
                slBox.PivotTables("pt_Email").TableRange1.Copy
                Sheets.Add After:=ActiveSheet
                ActiveSheet.Paste
    
                Sheets("Email").Select
                Sheets("Email").Activate
            
        Else 'do nothing
    
        End If

    Next slItem

End Sub

 

[Domenic]

That's great, I'm glad you've got it working with HasData. I'm curious, though, if you try to do it manually, does it work? Just in case you're not sure how to do it, try the following...

1) Right-click on any cell within the pivot table.

2) Select PivotTable Options.

3) In the Data tab for the 'Number of items to retain per field', select None.

4) Click OK

 

[Seba Robles]

Oh so that's what that part of the code does. Yeah the pivot table was set to none for number of items to retain per field.

I think I didn't explain it to clearly. So I have two other pivot filters active, and the slicer will "grey out" the items that are not applicable to those active filters. When I said the slicer items had no data, I meant they didn't have data with those active filters. But if I were to remove those filters, the slicer items that were greyed out would have data in the source. (if that makes sense)

So that's why your code was refreshing the slicer/pivot, but it wasn't really removing any of the slicer items... because they technically do have data, just not for the active filters selected.

 

[Domenic]

Oh I see, no problem.

So HasData is the way to do. That's great.

Cheers!

 

[Seba Robles]

Thank you so much Domenic! Have a great day! ?

 

[Domenic]

You too have yourself a great day, cheers!