VBA - Loop through each visible slicer item

Seba Robles

Board Regular
Joined
May 16, 2018
Messages
61
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!!

VBA Code:
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

Board Regular
Joined
May 16, 2018
Messages
61
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)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,659
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...

VBA Code:
If slItem.HasData then
 
Solution

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,659
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

Board Regular
Joined
May 16, 2018
Messages
61
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:

VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,659

ADVERTISEMENT

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

Board Regular
Joined
May 16, 2018
Messages
61
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,659

ADVERTISEMENT

Oh I see, no problem.

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

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,827
Messages
5,661,147
Members
418,619
Latest member
ibruzzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top