'How do I select a slicer option based on the slicer item number if my pivot table is based on a data model?
I have this data table that updates everyday with new data and the pivot table and slicer refresh automatically through a macro. Now, I wanted to write a macro to select the newly added slicer option and deselect the previous option, while not messing with the other earlier options that are also selected (multi-select slicer). The way I was going about it was to count the number of slicer items and then pass that index number to SlicerItems(i).select = True for the desired options. But it looks like you can only pass an array to data model based pivot slicer?
Here's my code
n = ActiveWorkbook.SlicerCaches("Slicer_Date").SlicerCacheLevels(1).SlicerItems.Count
'tried this
ActiveWorkbook.SlicerCaches("Slicer_Date").SlicerItems(n).Selected = True
'also tried this format
ActiveWorkbook.SlicerCaches("Slicer_Date").SlicerCacheLevels(1).SlicerItems(n - 3).Selected = False
Is there any workaround to pass the item number or any other way to accomplish this? Thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|