Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Slicers and VBA

Slicers and VBA

This page has also been published on The Microsoft Office Blog.

Juggling the SlicerCaches collection

For each slicer you add to your workbook, Excel adds a SlicerCache object too, which controls which Pivottable(s) your slicer controls.

So suppose we have a workbook with two Pivot caches. Pivotchache1 has two pivot tables: Pivottable1 and Pivottable2. As soon as you add a slicer to a Pivottable (even if the slicer ties to the same field of the Pivottable) you get a SlicerCache object tied to the pivotCache to which the pivot table belongs:

Hierarchy of the slicer and it's family
Hierarchy of the slicer and its family

Note that the above picture is not entirely correct. In the object model, PivotTables are in fact tied to a slicerCache, not to a slicer. But on the userinterface side of things it is the slicer the user actually sees, which is why I put that in-between.

The code below enumerates all slicer caches and their associated pivottables in your workbook:

Sub MultiplePivotSlicerCaches()
    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As PivotTable
    Dim oSh As Worksheet
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.PivotTables
            oPT.Parent.Activate
            MsgBox oSlicercache.Name & "," & oPT.Parent.Name
        Next
    Next
End Sub

As soon as you check more than one Pivottable on the Pivottable Connections dialog of a Slicer, the slicers in question will share a single SlicerCache object. The other SlicerCache object will be removed from the collection. This explains why you cannot go back by unchecking all but one Pivottable in that dialog: all joined slicers will now be changed by changing the checked Pivottable(s) on any of them. In turn, each checked Pivottable becomes part of the Pivottables collection of the remaining SlicerCache object.

If you decide to select Slicer1 and change its pivot connections by checking both Pivottable1 and Pivottable2, one slicercache is deleted (the one beloning to the Pivottable you checked to add to the current slicer). So the hierarchy changes to:

Changed hierarchy of slicers
Changed hierarchy of slicers

So both Slicer1 and Slicer2 control Pivottables 1 and 2. The slicers 1 and 2 are in sync too because it is in fact the SlicerCache that is changed by the slicer. So the hierarchy in the picture above isn't entirely true.

Changing the buttons using VBA

It is easy enough to change the button appearance using a bit of VBA:

Sub AdjustSlicerButtonDimensions()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
         'Note that changing the ColumnWidth also affects the Width of the slicer itself
         'So the next line will change the ColumnWidth!
        .Width = 300
     End With
End Sub

Note that the numbers do not coincide with what is shown on the ribbon. Apparently the unit of measure differs between VBA and the ribbon.

Changing the slicer itself and some SlicerCache properties

Changing some aspects of your slicer using VBA isn't hard to do. In fact, the macro recorder makes finding out how this works relatively simple. After changing some settings and doing a bit of tidying up I got:

Sub AdjustSlicerSettings()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .Caption = "City"
        .DisplayHeader = True
        .Name = "City 2"
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_City2")
        .CrossFilterType = xlSlicerNoCrossFilter
        'xlSlicerCrossFilterShowItemsWithDataAtTop:
        '   Visually indicate items with no data, items with data are pushed to the top
        'xlSlicerCrossFilterShowItemsWithNoData:
        '   Visually indicate items with no data, items with no data stay put
        'xlSlicerNoCrossFilter:
        '   No indication for items with no data.
        .SortItems = xlSlicerSortAscending
        .SortUsingCustomLists = False
        .ShowAllItems = False
        '   This ensures that data no longer in the pivot cache is not shown on the slicer
    End With
End Sub

Next: synchronising pivottables


 


Comments

Showing last 8 comments of 157 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (6/30/2016 7:05:42 AM)

Hi Jerry,

What if you remove the inner loop and just let it report the SlicerCache names?

 


Comment by: Jerry (6/30/2016 12:17:09 PM)

Jan,

Thank you! Not a MAC issue at all. Removing the inner loop did report a slicer that was located out of view I thought had been deleted and was not. Knowing the slicer name gave me a clue to which worksheet to search. Much appreciated!

Thank You,
Jerry

Sub MultipleSlicerCaches()
    Dim oSlicercache As SlicerCache
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        Debug.Print oSlicercache.Name
    Next
End Sub

 


Comment by: JonMorse (7/12/2016 10:38:13 PM)

Jan,

I have found alot of blogs about connecting Slicers to multiple data sources using VBA and pivot tables. In 2013, Excel added the slicer option to data tables. I have tried to find something that would allow me to filter two different data tables with some of the same column names using the same set of slicers (SlicerCache) but I can't get anything to work. I am not a real VBA guy but usually find that there is something that will help on the web.

any thoughts?

 


Comment by: Jan Karel Pieterse (7/15/2016 4:53:27 PM)

Hi Jon,

I have a note to write about the subject, but it'll take some time before I'll get round to it I'm afraid.

 


Comment by: Mads (10/18/2016 2:59:45 PM)

Hi Jan

I have a problem looping through all the filters in a slicer. Currently I am doing the loop on a single pivot table without using a slicer, but now I need to sort two pivot tables at once. The two pivot tables got data from the same source and it should sort the exact same thing for both tables and then save the sheet. So I thought that a slicer would be the easiest thing to do. but I can't make it work!!

The following code works perfectly on a single Pivot table (pivotItem)! how do I convert it to do the same on a slicer? and it should only loop through customers starting with KUM as the code suggest.


Sub xSave_KUM()

Application.ScreenUpdating = False

Dim Customer As PivotItem
Dim SubString As String
Dim strSheet As String

SubString = "KUM"

ActiveSheet.Select
    ActiveSheet.Copy
Range("B2").Value = Range("B2").Value

For Each Customer In ActiveSheet.PivotTables(2).PivotFields("Customer").PivotItems

        If InStr(LCase(Customer.Name), LCase(SubString)) <> 0 Then
            ActiveSheet.PivotTables(2).PivotFields("Customer").CurrentPage = _
            Customer.Name
                
On Error Resume Next

    With ActiveSheet.PivotTables(2)
        .EnableDrilldown = False
        .SaveData = False
    End With

    ActiveSheet.Select
    ActiveSheet.Copy
    ActiveSheet.Name = Customer.Name
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.DisplayAlerts = False
    cell = Right(Range("B2").Value, 67)
    Fpath = "F:\Strategy & projects"
    Fname = Fpath & Customer.Name & "_" & cell & ".xlsx"
    ActiveWorkbook.SaveCopyAs filename:=Fname
    ActiveWorkbook.Close

Else
        
    End If

Next Customer

Application.ScreenUpdating = True

End Sub



Hope you can help me in the right direction!

/Mads

 


Comment by: Jan Karel Pieterse (10/18/2016 4:58:05 PM)

Hi Mads,

Couldn't you use something like:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_Customer").SlicerItems
        'First remove all filters
        If oSi.Value Like "KUM*" Then
            ShowAllItemsExcept ActiveWorkbook.SlicerCaches("Slicer_Customer"), oSi
        End If
    Next

End Sub

Sub ShowAllItemsExcept(oSL As SlicerCache, oSi As SlicerItem)
    Dim oSi2 As SlicerItem
    For Each oSi2 In oSL.SlicerItems
        oSi2.Selected = True
    Next
    For Each oSi2 In oSL.SlicerItems
        If oSi.Name = oSi2.Name Then
        Else
            oSi2.Selected = False
        End If
    Next
End Sub

 


Comment by: Mads (10/21/2016 1:21:23 PM)

Hi Jan,

Thanks for the fast reply, it was really helpful!

First, I can actually make the loop work, but it is very slow! Is there anyway to make it faster? ScreenUpdating is not doing the job.

Second, I have no idea where to put my code, which copies and saves the sheet whenever it is a customer starting with "KUM".

/Mads


 


Comment by: Jan Karel Pieterse (10/21/2016 2:36:43 PM)

Hi Mads,

For startes, you might try replacing this:

    For Each oSi2 In oSL.SlicerItems
        oSi2.Selected = True
    Next


with:

oSl.ClearManualFilter
.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].