Excel Slicer connected to a Power Pivot - Select multiple slicer items

0
votes

I'm trying to make a report form that will ask what month and year you want the report to show. However, there is a specific pivot table that uses a four-year range to show trends data.

However, all the pivot tables on my workbook are connected through Power Pivot/Data Model. Using this source here, I was able to easily change slicers for single-items only.

An example that's working:

ActiveWorkbook.SlicerCaches("Slicer_Month2").VisibleSlicerItemsList = "[Calendar].[Month].&[" & frmGen.cbMonth.Text & "]"

But when I used a loop to replace multiple selections in the slicer, I've encountered an error after it's done looping all of the selection, with an error '28': Out of stack space. 

    For i = 0 To 3 Step 1
        ActiveWorkbook.SlicerCaches("Slicer_Year").VisibleSlicerItemsList(i + 1) = "[Calendar].[Month].&[" & frmGen.txtYear.Text - 3 + i & "]"
    Next

Since there's already four that was selected on the slicer, it should have been able to replace it, which is also shown when I added the code onto the watch window. But it always show that error after the loop, even if I lower the loop count.

Is there another way of selecting multiple items in a slicer that's connected through Power Pivot?

1
excelvbapivot-tablepowerpivotslicers
check this answer it might help you. – Damian
Oh wow, I didn't realize I could just update one of the pivot tables and the slicer will sync it up with the other pivot tables as well, thanks for the link got mine to work. – Basher

1 Answers

1
votes

Thanks to Damian for providing a clue to my problem, I simply had to update one of the pivot tables that's linked to the slicer and it updated the other pivots just fine. 

For i = 0 To 3 Step 1
    ar(i) = "[Calendar].[Year].&[" & frmGen.txtYear.Text - 3 + i & "]"
Next

ActiveWorkbook.Sheets("pg2").PivotTables("pvtPg2_SPARK").PivotFields("[Calendar].[Year].[Year]").VisibleItemsList = ar