CODEWITHSUNDEEP
excelvbaexcel-2010
Aberama Gold
Aberama Gold

Reputation: 68

How can I filter a slicer for one item through VBA?

My slicer contains over 100 cities and I'd like to filter the slicer through VBA on one city at a time (in order to print the region of the chart into a Powerpoint). The trouble is that I think I would have to list every other city and show them as "Selected = False". I need a code block where I only specify the city/cities I and the rest of the slicer options are "Selected = False" by default.

I've recorded the macro code below and I've searched the net for a solution to this problem but have come up empty-handed.

 With ActiveWorkbook.SlicerCaches("Slicer_City")
        .SlicerItems("New York").Selected = True
        .SlicerItems("Chicago").Selected = False
        .SlicerItems("Trenton").Selected = False
        .SlicerItems("Atlanta").Selected = False
        .SlicerItems("Houston").Selected = False
        .SlicerItems("Los Angeles").Selected = False
'  there are 100 more cities to list if I just wanted "New York" to be selected 
       'in the slicer
End With

I need code where I should only specify the selection (New York) and not declare every other city as "Selected = False".

Upvotes: 1

Views: 1488

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4824

The quickest way is to make the pivotfield of interest a pagefield, and then iterate through the items and set the .CurrentPage property of the PivotField to each in turn, as this automatically turns the other items to FALSE. If you don't want the PivotField in the Page Fields area, then you can still do this quickly by setting up a 'Master' PivotTable somewhere out of sight, putting the field of interest in the master as a PageField, connecting the Master PivotTable to PivotTable1 ('Slave') with a Slicer, and then changing the .CurrentPage property of the Master, which will then instantly filter the .Slave via the Slicer.

See my answer at VBA code to filter a pivot table based on the value in a Cell that has some efficiency tips for this kind of thing, as well as links to other answers containing code.

Upvotes: 1

Related Questions