VBA change Slicer item selection

excel slicers vba
Charlotte Wiatrowski picture Charlotte Wiatrowski · Jul 5, 2018 · Viewed 15.5k times · Source

I´m a newbie with VBA and need to learn how to change automatically the selected values on a Slicer. I first tried with a very simple one, but I tried every variation possible to the following code and always get an error 1004, this time "application-defined or object-defined error"

Sub SlicerSelect()
    With ActiveWorkbook.SlicerCaches("Slicer_Time")
        .SlicerItems("2016").Selected = False
    End With
End Sub

Does someone have an idea ? Here is also an image of my slicer and its settings.

By the way, it works when I'm using the .ClearManualFilter command.

Thanks a lot !

Here is also a macro record by filtering manually my items :

Sub Macro2()
' Macro2 Macro
ActiveWorkbook.SlicerCaches("Slicer_Time2").VisibleSlicerItemsList = Array( _
    "[Booking Period].[Time].[YEAR].&[2018]")
End Sub

Answer

jeffreyweir picture jeffreyweir · Jul 6, 2018

Your problem was that there are two different types of PivotTables:

  • PivotTables based on ranges, that use the kind of code you initially posted, and that let you pass in individual PivotItems one at a time; and
  • PivotTables based on the Data Model (i.e. PowerPivot) or OLAP cubes, that use a completely different syntax where you have to pass in an array showing ALL of the items you want visible, using a much more confusing syntax.

Related questions

Report connections for Pivot table slicer doesn't show all pivot tables?

I have 3 sheets with several pivot tables on each and one sheet with the source data. These 4 sheets were copied directly from another workbook. When I copied the worksheets over the source data for each pivot table was still the …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More