Accessing the Slicer through VBA

There may be times when we want to programmatically control slicers through VBA. For example, we may want to default a date to the current date or set a cost centre depending on who has opened the book. This post looks how a slicer can be controlled through VBA.

In this example, we have added a slicer to a worksheet that uses a date hierarchy as its source. Because, we have included all levels of the hierarchy when the slicer was setup, we get three individual slicers for each level of the hierarchy.

If we look at the settings for the slicer (right click on the slicer and select slicer settings), we can see that the slicer has a name and each level of the slicer hierarchy maintains the hierarchy level name. For example, the Slicer_Dates_Hie below has a level Year, Month and Day. Although we can change the name (for example the name Year in the picture below), the slicer retains the mdx level that the slicer belongs to.

Accessing the Slicer

We can access the slicer through the SlicerCaches object. This is as simple as declaring the slicer cache and referencing it to the name of the slicer we want to use. For example;

Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

Navigating the Structure of the Slicer

Once we have a reference to the slicer we can navigate its structure using SlicerCacheLevels. For example we can determine the number of levels of the slicer and iterate over them with the following code.

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

For Each sL In sC.SlicerCacheLevels
Debug.Print “Level ” + CStr(sL.Ordinal) + ” –> ” + sL.Name
Next sL

Naturally, the level can be accessed through the cache level ordinal to produce the same result. The highest level (year) takes the value 1 which increments for each level from the first level. There is always a level (ie level 1) even if the slicer is based on a single attribute.

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
For i = 1 To sC.SlicerCacheLevels.Count
Debug.Print “Level ” + CStr(i) + ” –> ” + sC.SlicerCacheLevels(i).Name
Next i

Slicer Data Members

We can gain access to the data items through slicer items, as mdx attributes, they have a caption, value and a key (member unique name). For example the year 2011 in this slicer has a value of 2011 and a name (MDX unique name) of [Dates].[Dates Hie].[Year].&[2011]

Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
Set SL = sC.SlicerCacheLevels(1)
Debug.Print “——————————————————————————”

For Each sI In SL.SlicerItems
Debug.Print “Caption –> ” & sI.Caption
Debug.Print “Value –> ” + CStr(sI.Value)
Debug.Print “Unique Name –> ” + sI.Name
Debug.Print “——————————————————————————”


Setting the Slicer Value

Slicer item selection must be set through the visible slicer items list and is specified using an array. For example, we could set the SlicerCache (selected items) to 2011 and 2012 with the following code;

sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]”, “[Dates].[Dates Hie].[Year].&[2012]”)

The name selected must be a data member of the level. If not a runtime error will occur (as below)

Once the values are set, connected pivots are updated immediately

Member Iteration

Members can be easily iterated using the following code;

Dim sC As SlicerCache 
Dim SL As SlicerCacheLevel 
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Dates_Hie") 
Set SL = sC.SlicerCacheLevels(2)
For Each sI In SL.SlicerItems    
 sC.VisibleSlicerItemsList = Array(sI.Name) 


The control of slicers through VBA could be used to provide some very nice personalisation to work books.

NB:  If you liked this post you might be interested in this one.  In it, I discuss setting slicers through cell association to pivot rows.


59 thoughts on “Accessing the Slicer through VBA

  1. Hi! Please
    can I get different format of a measure in a PowerPivot table based on Slicer Item value
    something like this. I did try but no luck. I can send a file if it needed

    thank you!!!

    If SlicerItem = “abs” Then

    With ActiveSheet.PivotTables(“PP002”).PivotFields( _
    .Calculation = xlNormal
    .NumberFormat = “# ### ##0_.”
    End With
    End If

    If SlicerItem = “ratio” Then

    With ActiveSheet.PivotTables(“PP002”).PivotFields( _
    .Calculation = xlPercentOfColumn
    .NumberFormat = “0.0%”
    End With
    End If

    • Hi 100sky,

      You can specify the format string for the pivot columns with code like this;

      Dim x As PivotTable
      Set x = Sheet2.PivotTables(1)
      x.DataBodyRange.Cells.NumberFormat = “#,##0”
      x.PivotFields(“[Measures].[End of Day Rate]”).NumberFormat = “#,##0.000”

      Note i can set the entire data area or only a particular field.

      Is this what you want?

  2. thank you!
    let’s we’ve got 2 store (facts)

    A 100
    B 200

    we can do a measure Sum:=sum(FT) and we can make 2 PP tables like
    A 100
    B 200
    A 33.3%
    B 66.7%

    What I want, if it possible:
    To create dimension and not link it to fact table, like


    and in One PP table after clicking by slicer item “ABS” getting format “100” but after clicking by slicer item “RATIO” getting format “33.3%” all in the same column

    I can get result what I need only by using Buttons&VBA and want to know if it’s possible by SlicerItems&VBA

    Thank you! Very much!

  3. I think what you want to do is change the way that the pivot table operates so that you can set the ‘value’ to be a percent of the column or the native value.

    So, I have two subs that specify raw values or percent values. You can then fire these how you like (not the only difference is in the calculation and number format)

    Sub RawValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1”)

    With x.PivotFields(“[Measures].[Sum of Value]”)
    .Calculation = xlNormal
    .NumberFormat = “#,##0”
    End With

    End Sub

    Sub PercentValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1”)

    With x.PivotFields(“[Measures].[Sum of Value]”)
    .Calculation = xlPercentOfColumn
    .NumberFormat = “#,##0%”
    End With

    End Sub

  4. Paul, thank you!

    Your macro is what I need.

    If it’s possible what when I have Slicer consist of 2 Slicer Items (“abs” and “ratio”) and clicking “abs” I’ve got macro “RawValues” execution and clicking “ratio” I’ve got macro “PercentValues” execution

    Thank you from Russia

    • I am not sure if you can attach the marco(s) to slicer items so that the events are fired with a change of a slicer value. What you may have to do is create a pivot table with the filter showing the ‘slicer selection’ and attach the slicer to it. Then, you can attach your macro to the on_change event on the worksheet so that you can check what the selected value of the slicer is and apply a check against the pivot values.

  5. Yes, I can return name(value) any of slicer items due to your article but can’t attach the macro to slicer item clicking directly. Now I know it definitely

    Your blog is a great help
    Thank you

    • Hi Vanessa, I am a little unsure of exactly what you are after. But doesn’t the following help?

      Sub MemberInfo()

      Dim sC As SlicerCache
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Month”)
      Debug.Print “There are ” + CStr(sC.SlicerCacheLevels(1).Count) + ” elements in your slicer”

      Dim sCL As SlicerCacheLevel
      Set sCL = sC.SlicerCacheLevels(1)

      Dim i As Integer
      For i = 1 To sCL.SlicerItems.Count

      Debug.Print “Slicer Caption -> ” + sCL.SlicerItems(i).Caption
      Debug.Print “Slicer Value -> ” + sCL.SlicerItems(i).Value
      Debug.Print “Unique Name –> ” + sCL.SlicerItems(i).Name
      Debug.Print “—-”

      Next i

      End Sub

      • Hi Paul
        I tried using the above code in a module
        but I get the following error on the first Debug – Line 3
        Run-time error ‘1004’
        Application-defined or object-defined error

        Any ideas

  6. Hello!

    I was trying to set the Slicer Value as you said before:

    “sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]“, “[Dates].[Dates Hie].[Year].&[2012]“)”

    but i found VisibleSlicerItemsList is read-only.

    My data source is OLAP and when you are working with OLAP, I think there are some properties that are read-only. Do u know another way to set the slicer value?

    Thanks in advance and regards!

    • Hi Emilio,

      I can set the slicer (against a cube) values with these snippets;

      ‘ finally set it to 2005
      Sheet1.Cells(2, 2) = “[Date].[Calendar Year].&[2005]”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]”)

      ‘ multiple set
      Sheet1.Cells(2, 2) = “Multiple”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]”, “[Date].[Calendar Year].&[2006]”)

      I’ll also flick you through the workbook.

      • Hello Paul, I got it!

        I tried your solution and it works fine. The problem was I was setting de VisibleSlicerItemList of the SlicerCacheLevel instead of the SlicerCache.

        Thank you very much and kind regards,

      • Dear Sir – I need to have macro to select next item in slicer while running macro.

        Below is the code when i see while recorded.

        ActiveWorkbook.SlicerCaches(“Slicer_Fiscal_Calender”).VisibleSlicerItemsList = Array(“[01 – Time].[Fiscal Calender].[Fiscal Year].&[2016].&[Q2].&[M05].&[201621]”)

        What it need is, i need to have slicer selected to 201622 (from 201621)

  7. Does your site have a contact page? I’m having trouble locating it but, I’d like to shoot
    you an e-mail. I’ve got some suggestions for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it expand over time.

  8. Pingback: Pivot Filtering with Cell Selection and VBA « Paul te Braak

  9. Pingback: Dynamic DAX Query Tables in Excel 2013 « Chris Webb's BI Blog

  10. Pingback: Dynamic DAX Query Tables in Excel 2013 - SQL Server Blog - SQL Server - Telligent

  11. Pingback: Dynamic DAX Query Tables in Excel 2013 - Atlas Analytics Inc.

  12. Pingback: Dynamic DAX Query Tables in Excel 2013 | Best Analytics

  13. I was very happy to find this site. I need to to thank you for ones time just for this fantastic read!

    ! I definitely really liked every bit of it and I have you
    book-marked to look at new information in your web site.

  14. Hi,
    I do have a VBA giving me a list of slicers left, according any selection done in related slicers:
    Sub GetSlicerValues01()
    Dim lCt As Long
    Dim oSi As SlicerItem
    With ActiveSheet.Range(“A1”)
    .Value = “Slicer value”
    .Offset(, 1).Value = “Selected”
    .Offset(, 2).Value = “Available”
    For Each oSi In ActiveWorkbook.SlicerCaches(“Slicer_Belt_series”).SlicerItems
    lCt = lCt + 1
    .Offset(lCt).Value = oSi.Value
    .Offset(lCt, 1).Value = oSi.Selected
    .Offset(lCt, 2).Value = oSi.HasData
    End With
    End Sub
    But I actually just want a number back, telling me the amount of slicer left. It should be easy just to count where Selected = true AND HasData = true.

    Any suggestions how such code could look like?

    Thanks in advance JHN

    • Hi JHN,

      You could create a function to return the count of selected items. This should be ok.

      Function SelectedCount() As Integer

      Dim sc As SlicerCache
      Dim sl As SlicerCacheLevel
      Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
      Set sl = sc.SlicerCacheLevels(1)

      For i = 1 To sl.SlicerItems.Count
      If sl.SlicerItems(i).Selected = True Then SelectedCount = SelectedCount + 1

      End Function



  15. Hi, I would like to pose another question on the same subkect of Data Slicers. I have searched around and have not been able to find out if any Slicer Items have been clicked or changed.

    I basically have a complex pivot and correspoding chart, but the chart series keep chaging to default. I have worked out the VBA code t set the series type and colour but this only works when I click a button.

    I need to run this reset macro code when the user click any of the items in the two or 3 slicers I have.

    Could any one help. Thanks Kuldip

    • Hi Kuldip,

      I think the easiest way is to have your slicers put their data to different worksheet (almost like a control sheet you might say). On that worksheet, you can then listen for the worksheet_Change event to update the charts, names etc. There won’t be any need for a different button.

      HTH, Paul

  16. Hi Paul,
    Many Thanks for the suggestion. The issue is that the contents of the data slicer and pivot keep changing so not to static. Much appreciated, however, I managed to find a good solution :

    I used Worksheet_PivotTableUpdate
    So when ever the pivot changed I used this as a trigger to update the series type and colour and works great.

    The following is what I used to set the series attributes.
    I = 1
    Set seriesCol = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
    For Each mySeries In seriesCol
    Set mySeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(I)
    With mySeries
    ‘MsgBox “Series Name : ” & .Name
    Select Case UCase(.Name)
    .ChartType = xlColumnStacked
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(102, 153, 255) ‘ACTUAL HEAD BOOKING (Blue)
    .Transparency = 0
    End With
    Case Is = “EQUIVALENCE”
    ‘Etc.. Etc… Etc…
    End Select
    End With
    I = I + 1


    Thanks again. Regards Kuldip.

  17. Hi Paul, I have another question that I am struggling with.

    Hi I having difficulty trying to cycle through the slicers to see if they are filtered or not.

    My goal is to get all the selected Slicer into a worksheet so that I can apply a (High to Low) filter to the underlying pivot data so that I can pick the “Top 5 over budget” based upon the selections made in the Data Slicers.

    I have the following code but get see error:
    ‘Run Time Error 438’ Object doesn’t support this method’
    Can someone advise how I can achieve this.

    Public Sub top_over_under_booked()
    Dim oSi As SlicerItem
    Dim oSlicercache As SlicerCache
    Dim oSl As SlicerCacheLevel
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set target_ws = ThisWorkbook.Worksheets(“Get Slicer Selections”)
    For Each oSlicercache In ThisWorkbook.SlicerCaches

    For Each oPt In oSlicercache.PivotTables

    oPt.Parent.Activate ‘Slice Name
    worksheet_name = UCase(oPt.Parent.Name)
    If worksheet_name = UCase(“Chart Analysis 5 Years”) Then
    column_no = 0
    slicer_name = UCase(oSlicercache.Name)
    Select Case UCase(oSlicercache.Name)
    Case Is = “SLICER_FY1”
    column_no = 1
    Case Is = “SLicer_REPORT_PT_DEPT1”
    column_no = 2
    End Select
    If column_no 0 Then
    For Each oSl In ActiveWorkbook.SlicerCaches(oSlicercache.Name) ‘ <—– Error
    For Each oSi In oSl.SlicerItems
    'oSi.Selected = True
    check_slicer_string = oSi.Value
    'target_ws.Cells(ource_ws.Cells(65000, column_no).End(xlUp).Row + 1, column_no) = oSlicercache.Value
    End If
    End If
    End Sub

    • Hi Kuldip – I’ve attached a sample of how this could be done. We just create a function to iterate over the level, then if any items are found to be not selected we know that all are not.


      Sub MyControl()

      Debug.Print AllSelected(“Slicer_Customer_Geography”, 1)

      End Sub

      Private Function AllSelected(ByVal SlicerCacheName, ByVal SlicerCacheLevel) as Boolean

      Dim sC As SlicerCache
      Dim sL As SlicerCacheLevel
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Customer_Geography”)
      Set sL = sC.SlicerCacheLevels(SlicerCacheLevel)

      ‘ Lets assume that all are selected
      For i = 1 To sL.SlicerItems.Count
      If sL.SlicerItems(i).Selected = False Then
      AllSelected = False
      Exit Function
      End If

      AllSelected = True
      End Function

  18. Hi Paul,

    I have so nailed a great solution and works 95% of the time. As mentioned in my original question, I created the Pivot chart and through VBA reset the series colours, type etc. I subsequently used :


    to trigger the reset_colours_series_type etcBUT the problem is that these are instances when the associated PIVIT Data changes but the inbuilt function Worksheet_PivotTableUpdate DOES NOT trigger the code to be run and the chart remain in bad configuration. Wierd indeed.

    Does anyone have any ideas why this triiger doesn’t trigger (so to speak).

    Thanks in advance Kuldip Mond.

  19. Hi Paul,

    Thanks so much for the great post. I was wondering if there is a way to write a macro that can iterate through a list of items in a slicer, selecting two items at a time. My end goal is to select every possible combination of two items within that slicer. I’ve looked everywhere for the answer to this, but can’t seem to find it. My data is linked to PowerPivot. Thanks!

  20. Hi Linda,

    Yes you can do it. The code below will iterate over every combination. Also note that since the order of selection is not important (ie (2005,2006) is the same as (2006,2005)) you only have to iterate over a reducing number of elements as you move down the list. this is why the second loop only moves from the current index position to the end of the list. I hope that makes sense?

    Sub PlayMe()

    Dim sc As SlicerCache
    Dim sl As SlicerCacheLevel
    Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Date.Calendar_Year”)
    Set sl = sc.SlicerCacheLevels(1)

    For i = 1 To sl.Count

    For j = i + 1 To sl.Count

    Debug.Print sl.SlicerItems.Item(i).Name & “||” & sl.SlicerItems.Item(j).Name
    sc.VisibleSlicerItemsList = Array(sl.SlicerItems.Item(i).Name, sl.SlicerItems.Item(j).Name)

    Next j

    Next i

    End Sub

    • Yes, that’s exactly what I was looking for — thank you!! I do have one quick question though. I understand what you mean when you say that the second loop only moves from the current index position to the end of the list in order to avoid selecting duplicate combinations. How can I extend this idea to capture every possible combination of 3 items in a slicer? I thought that the For..Next statements would look like:

      For i = 1 To sl.Count

      For j = i + 1 To sl.Count

      For k = j + 1 To sl.Count

      sc.VisibleSlicerItemsList = Array(sl.SlicerItems.Item(i).Name, sl.SlicerItems.Item(j).Name, sl.SlicerItems.Item(k).Name)

      Next k

      Next k

      Next i

      However, this code generates duplicate combinations. What do I need to change?

      Thanks again for your help!

  21. No, I just realized I am not getting duplicates. I wrote a macro that records each combination of the selections your code makes on a worksheet, and I was originally seeing duplicates because of a mistake in the way I wrote that macro, not in the above code. I’ve fixed it now and it’s working exactly how I need it to. Thank you so much for taking the time to help me!

  22. Hi Paul,

    Great info, thank you for sharing your knowledge! Hey I am using XL 2013 and have a dashboard that has a country slicer (ie..France, Germany, US, etc..) and pivots connected to an SSAS cube. This slicer continues to show all items (countries) even if there is no apparent data, and even if I use the hide items with no data setting. Have even tried using custom data source order to put those to bottom of list but is seems like slicers connected to a cube behave differently. Even if I set up a pivot with just the countries I wanted filtered in rows, with no measures, and create slicer, it still gives me all. Basically I want to have a slicer that only shows the items I want shown, so wondering if there is a solution to this? Couple thoughts:

    1. Can you hide or make particular items (countries) not visible in a slicer using vba?

    2. Could I create a custom slicer in PowerPivot and then somehow pass the selection from that slicer to the Slicer I don’t want to show (the one with all countries) that controls the pivots?

    Any thoughts you have on this would be greatly appreciated! I have searched and searched online and just can’t seem to find a solution.

    Best Regards,


  23. For those looking to Sync an OLAP slicer to a non-OLAP slicer the below code will help you get it done:

    Welp, after about a week of frustration I finally have a solution to this one… for all those looking to do the same thing the below code will sync your OLAP slicer to a non-OLAP slicer. 🙂

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim scOLAP As SlicerCache
    Dim scList As SlicerCache
    Dim sO As Slicer
    Dim sL As Slicer
    Dim si As SlicerItem
    Dim i As Integer
    Dim svalue As String
    Dim ar() As String

    Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
    Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)


    Set sO = scOLAP.Slicers(1)
    Set sL = scList.Slicers(1)
    ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
    svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), “[CleanedData].[RegionCode].&[“, “”), “]”, “”)
    ar(i) = svalue
    For Each si In scList.SlicerItems
    If UBound(Filter(ar, si.SourceName)) < 0 Then
    si.Selected = False
    End If
    End Sub

  24. Hi Paul – very helpful blog. Any way to pause this before proceeding to the next slicer item?
    Issue is this loops too quickly before excel has time to fully load the data for one selection. Each item can take about 15 secs to load. Also how do I unselect at the end?

    Dim sc As SlicerCache
    Dim sl As SlicerCacheLevel
    Set sc = ActiveWorkbook.SlicerCaches(“Slicer_One”)
    Set sl = sc.SlicerCacheLevels(1)

    For i = 1 To sl.Count
    sc.VisibleSlicerItemsList = sl.SlicerItems.item(i).Name
    Next i

  25. Hi Paul – figured out one way by adding Application.CalculateUntilAsyncQueriesDone in the loop. Now how can I make this code more scalable to do the same, but loop thru all other slicers without having to modify or create new subroutines each time?

  26. Hi,
    Can you help me with this, I have an OLAP report that I created that is run by slicers. For example,

    Dept 1 slicer
    Dept 2 slicer
    Dept 3 slice

    Its working OK now like this, when I click on the Dept 1 for instance the OLAP report will run pulling all items related to Dept 1. and so on.

    Now I want to ues VBA to click on each slicer instead of manually doing it.

    Do you have a code I can use.

    Thanks for your help.

  27. Hi,

    Before I give a description of the problem, here is the list of technologies I am using – Excel 2010, PowerPivot Add-in, Win 7, Macros – VBA

    I have created a reports using multiple data sources (via PowerPivot). PowerPivot has provided Slicers and I want to print various states of the report to separate PDFs for each value of the Slicer. I have already written the code that does the printing job. I need help with a snippet of code to loop through the list of Slicers and select them in such a way that the state of the report changes everytime.

    Through my research I found out that there is a property named SlicerItem.Selected which can be set to TRUE or FALSE to select a particular item in the Slicer.

    Any help is appreciated.


    Kofi Dankwah

  28. Hi,
    I am newbie for VBA and basically trying to use your code to create a macro to in layman terms something like clicking on each value of slicer .
    after reading the post and comments , I am using this code.
    Sub Slicervalue()

    ‘ Slicervalue Macro

    Dim sC As SlicerCache
    Dim SL As SlicerCacheLevels
    Dim sI As SlicerItem
    Dim i As Integer

    Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Con_PBP_2016”)
    Set SL = sC.SlicerCacheLevels(1)

    For i = 1 To SL.Count

    sC.VisibleSlicerItemsList = SL.SlicerItems.Item(i).Name
    Call GrabAssumptions
    Next i

    End Sub
    But it gives an Undefined object error at Set SL = sC.SlicerCacheLevels(1) .
    Not sure what I am missing.

  29. Can I ask you what I think is a simple question please, but I can’t find an answer that works…

    I have a slicer with 5 work area options, which works well. What I want is to place the slicer on a hidden sheet. Then use an image of the 5 work areas, with 5 invisible ‘labels’ placed over each work area on the image. When the user clicks on the image – which is actually clicking the invisible label, a ‘click’ action happens which selects that particular work area on the hidden slicer.

    Do you know how I’d write the VBA for this? I’ve been searching the internet for hours!


    • Hi Becky,
      Yes you can do this, you just need a macro for each image and then you just assign the macro to the image.
      Also you don’t have to rewrite the macro for each button. Just create a macro that takes the argument to set a value (and sets the slicer based on that value) . Then create a macro (the one you assign to the image) to call the first macro with the appropriate slicer item.


  30. Hi Paul, I appreciated your article (even it is already more than five years old)! Maybe you also have an idea how to fix the following situation: I’m trying to filter a slicer based on the active cell selected by the user. The cell content looks like this: “Material1|Material2|Material3”.
    As you can see in the code below, the input is transformed into an array which afterwards will be applied to the slicer. This works like a charm, until an item is not existing in the slicer list, eg Material1 to Material3 are part of the slicer, but the input is “Material1|Material4”. In this case, the Macro stopps with a run-time error. I believe, I need to find a way to test each input item vs the SlicerCache, but don’t know, how to do so. But maybe there are also other options?

    Dim ItmArray() As String
    ItmArray() = Split(ActiveCell.Value, “|”)
    For i = LBound(ItmArray) To UBound(ItmArray)
    ItmArray(i) = “[Activities].[Item].&[” & ItmArray(i) & “]”
    Next i
    ‘ActiveWorkbook.SlicerCaches(“Slicer_Item”).VisibleSlicerItemsList = Array(ItmArray())

    Looking forward for a response! Many thanks in advance!

    • Hi Frank,
      There are 2 simple ways to do what you want. You could write code to compare the 2 lists (the cell and the slicer values) and identify any items not present or you could just bundle them into an error handler. I guess it just depends how you want to treat your user?

      • I’ve modified the code to compare the slicer items with the item array list in a double loop (luckily, my array only consists of 1 to 6/7 values). If it finds a match, a 2nd array is filled, which finally will be applied to the slicer (without errors).
        Could you comment a bit more on your proposal to “bundle them into an error handler”? What do you mean by that?

        For Each sI In ActiveWorkbook.SlicerCaches(“Slicer_Item”).SlicerCacheLevels(1).SlicerItems
        For i = LBound(ItmArray1) To UBound(ItmArray1)
        ‘Check if selected item is part of Slicer cache. If existing, add to new filter array
        If CStr(sI.Value) = ItmArray1(i) Then
        ReDim Preserve ItmArray2(j)
        ItmArray2(j) = “[Activities].[Item].&[” & ItmArray1(i) & “]”
        j = j + 1
        End If
        Next i
        ActiveWorkbook.SlicerCaches(“Slicer_Item”).VisibleSlicerItemsList = Array(ItmArray2())

  31. Hello Paul,

    Thanks for the article. I’m trying to find the selected slicer items and use the string in another function. I use the following code

    Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim SL As SlicerCacheLevel
    Dim sI As SlicerItem

    Set SL = ActiveWorkbook.SlicerCaches(SlicerName).SlicerCacheLevels(1)
    For Each sI In SL.SlicerItems
    If sI.Selected = True Then
    GetSelectedSlicerItems = (sI.Value)
    End If
    End Function

    Dim sValue As String
    sValue = GetSelectedSlicerItems(“Slicer_HeaderTitle”)

    It gives me the following error

    Runtime error 1004
    Application-defined or object-defined error

    Can you help with this one?

    Thanks, Oskari

    • Hi Oskari,
      I think most of the code is right. The only thing i would do to debug is where you’ve got GetSelectedSlicerItems = (sl.Value), print sl.Value to make sure its iterating all the way through.
      Also i dont understand how your using this?
      Dim sValue As String
      sValue = GetSelectedSlicerItems(“Slicer_HeaderTitle”)

  32. Hi Paul,

    Great article,
    Im hoping you can help me. I have a pivot from an external source and I want to create a new file and create a sheet for each slicer item with the respective pivottable range. I have been able to adjust a part of the code that works fine without external sources, i just can’t seem to find how to adjust the code of the second part (singleOut) with external sources: I think i am almost there, except for the singleOut macro. This helps me selecting the slicer items one by one in order to make seperate sheets.

    Can you help me? Thank you so much!


    Sub generateSlicerReport()

    Dim pt As PivotTable
    Dim slcItm As SlicerItem
    Dim slclev As SlicerCacheLevel
    Dim newWB As Workbook, currentWB As Workbook
    Dim newSheet As Worksheet, aSheet As Worksheet

    Set currentWB = ThisWorkbook
    Set currentS = currentWB.ActiveSheet
    Set aSheet = ActiveSheet
    Set pt = currentS.PivotTables(1)

    Set newWB = Workbooks.Add

    For Each slclev In ThisWorkbook.SlicerCaches(“Slicer_Company_Code”).SlicerCacheLevels
    For Each slcItm In slclev.SlicerItems
    Call singleOut(slcItm)
    Set newSheet = newWB.Sheets.Add(After:=newWB.Sheets(newWB.Sheets.Count))

    newSheet.Name = Replace(Replace(slcItm.Name, “[“, “”), “]”, “”)

    newSheet.Range(“A1”).PasteSpecial xlPasteValues


    Set slcItm = Nothing
    Set newSheet = Nothing
    Set aSheet = Nothing

    End Sub

    Sub singleOut(ByVal slcItm As SlicerItem)
    Dim tempSlcItm As SlicerItem

    slcItm.Selected = True

    For Each tempSlcItm In slcItm.Parent.SlicerItems
    If tempSlcItm.Name slcItm.Name Then tempSlcItm.Selected = False

    End Sub

  33. hi there, i’m somewhere between newbie and intermediate user of vba code.
    I have this sheet with 7 or 8 slicers and i would like to save current slicer values for restoring at a later date.

    it’s a printlist tool pr customer and the content is defined by the slicers, the content is semi permanent pr customer but i need to go through a list of customers and then redo the task next month. Instead of selecting 20 values in one slicer, 5 values in another, 2 in a third on each customer i wanted to do it .. one time… save it to a macro and be able to recall it with a macro.

    I’m just having trouble wrapping my head around the slicer code in general.

    any pointers? tips? links? you could throw at me ? 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s