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

All comments about this page:


Comment by: General Ledger (1/12/2011 7:22:29 PM)

WOW!! How did you discover all this great stuff? Thanks for revealing these secrets. Please keep this information coming.

GL

 


Comment by: Rick Freitas (2/9/2011 4:28:04 PM)

Hi Jan,

I noticed in the slicer settings box there is a "Use Custom Lists when sorting" check box. Do you know how you call a custom list to sort items in slicer box? Assuming probably VBA code to do that but haven't been able to find any examples. Thanks for your help with this.

Regards,

Rick

 


Comment by: Jan Karel Pieterse (2/10/2011 5:30:12 AM)

Hi Rick,

I assume this works exactly the same as "normal" custom lists are used when Excel does sorting. I'd look up custom lists in Excel help.

 


Comment by: Sebastien (5/18/2011 5:50:45 AM)

Hi,
I'm trying to get the selected items from a slicer using VBA.
I can't find how to use:
ActiveWorkbook.SlicerCaches("Slicer_xxxx").VisibleSlicerItems
or
VisibleSlicerItemsList
.
I am able to select items in a slicer, filling up the VisibleSlicerItemsList, but not get what is selected.
Do you have any ideas?

 


Comment by: Jan Karel Pieterse (5/18/2011 6:17:43 AM)

Hi Sebastien,

Have a look at this little macro, maybe it gets you going:
Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems
        MsgBox "Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
    Next
End Sub

 


Comment by: Sebastien (5/19/2011 1:02:40 AM)

I tried what you explained but I get "Run-time error '1004': Application-defined or object-defined error" because of SlicerItems.

Do you know how to fix that?

 


Comment by: Jan Karel Pieterse (5/19/2011 2:44:56 AM)

Which line is highlighted if you press debug?

If it is the msgbox line, hover your mouse over each part of that statement, which part shows the error message in the tooltip?

 


Comment by: Sebastien (5/19/2011 3:28:41 AM)

The error comes on the line:
For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems

I tried to do
Dim oSc As SlicerCache
Set oSc = ActiveWorkbook.SlicerCaches("Slicer_City")
For Each oSi In oSc.SlicerItems
...

And the error comes on the For Each line.
That's why I think the problem comes from SlicerItems.

 


Comment by: Jan Karel Pieterse (5/19/2011 6:37:48 AM)

Hi Sebastien,

Odd, as it works flawlessly for me. Does the slicer actually have any items visible?

 


Comment by: Sebastien (5/19/2011 6:48:17 AM)

Yes, I tried with different slicers with some selected items or not.
I don't really understand this error.
Could it be a security issue or something like that?

 


Comment by: Jan Karel Pieterse (5/19/2011 7:40:28 AM)

Hi Sebastien,

Send me the file in question and I'll have a quick look.

See email address near bottom of this page.

 


Comment by: Jan Karel Pieterse (5/22/2011 9:52:40 PM)

Hi Sebastien,

Found the problem. If your Pivot table is getting data from an external source, you must use the SlicerCacheLevels collection to find out what items are in the slicer:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    Dim oSl As SlicerCacheLevel
    For Each oSl In ActiveWorkbook.SlicerCaches("Segment_Date").SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
            MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        Next
    Next
End Sub

 


Comment by: Andrew Fedor (6/2/2011 7:48:12 PM)

I have 2 pivot charts in the same dashboard that are from 2 different datasets. In other words the tables that the pivot tables were bulit from are different. Is there a way to a single slicer to control both pivot charts? One chart is sales and the other is backlog and I want to look at both by salesman. So when I am looking salesman#1, I want to see sales for salesman#1 and backlog for the same salesman.

 


Comment by: Jan Karel Pieterse (6/5/2011 11:55:44 PM)

Hi Andrew,

As far as I know, Each pivot cache has its own set of slicers, so the short answer is no.
But you could use some VBA code to synch two slicers.

For example, this code -placed in the ThisWorkbook module- will synchronize two slicer caches. The code only responds to a change in a slicer named Slicer_City, located on Sheet2 and will update Slicer_City2 located anywhere:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If Sh.Name = "Sheet2" Then
        With SlicerCaches("Slicer_City")
            For Each oSl In .SlicerItems
                SlicerCaches("Slicer_City2").SlicerItems(oSl.Caption).Selected = oSl.Selected
            Next
        End With
    End If
End Sub

 


Comment by: Andrew Fedor (6/6/2011 1:23:04 PM)

Jan:

Thank you for your assistance. I replaced your sheet and slicer names with mine from my workbook, but when I execute the code I get an error on line 6 "SlicerCaches("Slicer_City2....."

The error is -- Method 'Selected' of object 'SlicerItem' failed.

I checked the spelling of my sheet and slicer names and they are correct.

Any ideas.

 


Comment by: Jan Karel Pieterse (6/7/2011 12:15:29 AM)

Hi Andrew,

If you email me your workbook I'll try to find a bit of time to look at it. Please add which slicers you want to work together (where they are located in the workbook)

 


Comment by: Ian Tibot (7/6/2011 5:35:03 AM)

Hi Jan,

I have the same issue as Andrew, my data is coming from an external source but from two different queries that have common elements. If you have found a way to effectively make a second slicer a slave to a 'master' slicer that would solve my problem.

I've tried to use the code that you proposed for Andrew but when i go to run the macro it opens the run macro box and i have nothing to run.

Cheers

Ian

 


Comment by: Jan Karel Pieterse (7/6/2011 6:49:44 AM)

Hi Ian,

Try if code like this does any better.
Note that this code should fire off automatically once you change a slicer.
It is up to you to adapt the code to your slicer names and such!

Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "Charts" Then
        mbNoEvents = True
        With SlicerCaches("Slicer_Salesman")
            For Each oSl In .SlicerItems
                If SlicerCaches("Slicer_Salesman2").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("Slicer_Salesman2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

 


Comment by: Jan Karel Pieterse (7/6/2011 6:50:33 AM)

Hi Ian,

NB: The code goes into the ThisWorkbook module!

 


Comment by: Shadi Al. Kurdi (7/27/2011 11:11:36 PM)

Hi Jan,

is there any possibility to do Custom sorting in Slicer items by VBA code?

The only options available are A-Z or Z-A

BR
Shadi

 


Comment by: Jan Karel Pieterse (8/22/2011 3:01:54 AM)

Hi Shadi,

I have not tested this, but you might try adding a custom sort list that is in the order you want and then do the sort? Sort lists can be accessed through File, Options, Advanced tab, General category, Edit custom lists option.

 


Comment by: RealParadox (10/29/2011 8:13:06 AM)

I have problem with osi.selected = false

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    Dim oSl As SlicerCacheLevel
    For Each oSl In ThisWorkbook.SlicerCaches("Slicer_Year").SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
        ''MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        If oSi.Value = "2011" Then
         MsgBox "Yes"
         oSi.Selected = True
        Else
            MsgBox "No"
        End If
         '' MsgBox "Slicer name: " & oSl.Name & ", Slicer value: " & oSi.Value & ", Selected: " & oSi.Selected & ", Available: " & oSi.HasData
        Next
    Next
End Sub

maybe someone can help me?

 


Comment by: Jan Karel Pieterse (10/31/2011 1:07:57 AM)

Hi RealParadox,

What exactly is the problem?

 


Comment by: David (2/4/2012 9:08:40 AM)

Thank you so much for this resource! I have been searching everywhere for a way to link my slicers that do not share a cache with no success until now!

I modified the code provided to work with three slicers that have different caches. Unfortunately I have to run the "For Each" section twice, otherwise about 20% of the time it selects all of the slicer fields, or nearly all of them. Any idea why?

Dim mbNoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "Sheet1" Then
        mbNoEvents = True
        With SlicerCaches("Slicer1")
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

 


Comment by: Midimal (2/28/2012 4:02:22 AM)

Hi! Does anyone know how to determine using VBA if slicer filter is on or not? (similar what is possible with autofilter)

thanks

 


Comment by: Jan Karel Pieterse (3/5/2012 5:12:11 AM)

Hi Midimal,

YOu could use this function to detect whether or not a slicers is in filter mode:

Function IsSlicerFiltered(oSl As Slicer)
    Dim oSi As SlicerItem
    For Each oSi In oSl.SlicerItems
        If oSi.Selected = False Then
            IsSlicerFiltered = True
            Exit Function
        End If
    Next
End Function

 


Comment by: bill (3/29/2012 3:19:18 PM)

Can someone tell me why I receive an 'application defined or object defined error' when I try to set a slicer selected mode to 'True' or 'False'? (example in below code: oSi.selected = True)

Dim oSi As SlicerItem
Dim oSl As SlicerCacheLevel
Dim selected As Integer
selected = 0
Dim slicerDateVal As Date
Dim slicerDateText As String
    
    
Dim i As SlicerCaches
Dim k As Slicer
    
    Application.EnableEvents = False
    'ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").ClearManualFilter
     For Each oSl In ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").SlicerCacheLevels
         For Each oSi In oSl.SlicerItems
             slicerDateVal = oSi.Caption
             slicerDateText = Format(slicerDateVal, "yyyy-mm-dd")
             If slicerDateVal >= startDate And slicerDateVal <= endDate Then
                'oSi(slicerDateVal).selected = True
             If oSi.selected = False Then
                oSi.selected = True
             End If
            

 


Comment by: Jan Karel Pieterse (3/30/2012 1:54:14 AM)

Hi Bill,

Not sure why, but perhaps the item in question is greyed out on the slicer because of other filters being in place?

 


Comment by: Mark F. (4/3/2012 9:26:35 AM)

I cannot find the spot to change the format of numbers in the slicer. Currently I am getting very generic looking numbers all left justified. Any solutions to this?

 


Comment by: Jan Karel Pieterse (4/3/2012 9:54:29 PM)

Hi Mark,

The number format of a slicer cannot be adjusted I'm afraid.

 


Comment by: Dan (5/17/2012 4:46:15 AM)

Hi Jan,

I am trying to make one slicer change when another similar slicer changes. This is like other people above because I have information from more than one data source. I have ammended your code above for my workbook, the sheet is called "WWNI dashboard" and my slicers are called "ww_network_manager 3" and "ww_network_manager 4"

So I have this code in the "ThisWorkbook"



Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "WWNI dashboard" Then
        mbNoEvents = True
        With SlicerCaches("ww_network_manager 3")
            For Each oSl In .SlicerItems
                If SlicerCaches("ww_network_manager 4").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("ww_network_manager 4").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

Nothing happens when I change the "ww_network_manager 3" slicer. Can you help identify what I am doing wrong please?

Thanks,
Dan

 


Comment by: Jan Karel Pieterse (5/18/2012 7:21:55 AM)

Hi Dan,

What happens if you put a breakpoint in the code (first possible line) and then change the slicer that is supposed to trigger the event?

 


Comment by: Dan (5/18/2012 12:01:08 PM)

If I put a breakpoint on "If mbNoEvents Then Exit Sub" line and click the slicer it goes to my vba, I can then step through line by line and it runs fine. I have made a few modifications below to make this happen as my sheetname was the name of the sheet where the slicers are, however it does not change the slicer selections. My pivots are made with powerpivot, I'm not sure if that makes a difference. I can send a copy of the spreadsheet if you like.

Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    'IN case a slicer item in slicer 1 is not present in slicer 2
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Left(Sh.Name, 8) = "Data for" Then
        mbNoEvents = True
        With SlicerCaches("Slicer_ww_network_manager3")
            For Each oSl In .SlicerItems
                If SlicerCaches("Slicer_ww_network_manager4").SlicerItems(oSl.Caption).Selected _
                 <> oSl.Selected Then
                    SlicerCaches("Slicer_ww_network_manager4").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                End If
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

 


Comment by: Jan Karel Pieterse (5/20/2012 11:18:34 PM)

Hi Dan,

What happens if you remove the On Error Resume Next statement? It will mask any problems in the code.

 


Comment by: 100tsky (7/16/2012 2:15:52 AM)

Hi!

Can I get different format of measure based on value of slicer items, something like that:

If SlicerItem = "abs" Then


With ActiveSheet.PivotTables("PP01").PivotFields( _
        "[Measures].[sum]")
        .Calculation = xlNormal
        .NumberFormat = "# ### ##0_ð_."
    End With
End If

If SlicerItem = "ratio" Then

With ActiveSheet.PivotTables("PP01").PivotFields( _
        "[Measures].[sum]")
        .Calculation = xlPercentOfColumn
        .NumberFormat = "0.0%"
    End With
End If


thank you!!!

 


Comment by: Jan Karel Pieterse (8/7/2012 11:35:45 AM)

Hi 100tsky,

I doubt if you can do that, but you could tie the code you show to two buttons to mimick the behaviour?

 


Comment by: Paul Kelly (10/2/2012 11:47:39 AM)

Would like to show what slicers (filters have been applied) e.g. If Customer filter and Joe Bloggs and AN Other have been selected - show at the top the slicer that has been applied.

 


Comment by: Jan Karel Pieterse (10/2/2012 1:13:21 PM)

Hi Paul,

Well, of course the pivot table itself does show whether or not filters have been applied and if it is only one item, it shows the item.

I seem to recall one of the CUBE worksheet functions can be tied to a slicer, but I have not tried this, nor do I know whether this can be done to any slicer.

 


Comment by: Trish McDermott (12/7/2012 12:11:04 AM)

Hi there!
thanks for a really simple explanation on slicer cache...well done.

Now I don't want a message box to appear, but the slicer value to appear in a cell... how can I do this? I'm tearing my hair out!

It is probably really simple, I must be missing something.

I have one pivot table and 1 slicer in my workbook.

Cheers
Trish

 


Comment by: Jan Karel Pieterse (12/7/2012 1:04:56 PM)

Hi Trish,

Like so perhaps?

Sub GetSlicerValues()
    Dim lCt As Long
    Dim oSi As SlicerItem
    Worksheets.Add
    With ActiveSheet.Range("A1")
        .Value = "Slicer value"
        .Offset(, 1).Value = "Selected"
        .Offset(, 2).Value = "Available"
        For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_City").SlicerItems
            lCt = lCt + 1
            .Offset(lCt).Value = oSi.Value
            .Offset(lCt, 1).Value = oSi.Selected
            .Offset(lCt, 2).Value = oSi.HasData
        Next
    End With
End Sub

 


Comment by: Bill (12/8/2012 11:42:53 PM)

Thank you for these explinations. I like the last code snippet you gave to Trish. When combined with a "Worksheet_PivotTableUpdate" Subroutine the code helped me understand the slicer actions better.

 


Comment by: Bill (12/9/2012 12:25:41 AM)

So my question would be... if I have the code you suggested for Trish wrapped in a "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" subroutine, how could I have it cycle through and print out information for all of the slicers associated with the PivotTable that was associated with the slicer that activated the macro. Also, I can't seem to get out the "Parent" value for a slicer item. I keep getting "Run-time error '1004' Application-defined or object-defined error". Thanks in advance.

 


Comment by: Jan Karel Pieterse (12/10/2012 8:29:16 AM)

Hi Bill,

Actually, it is the pivot table that activates the event routine, not the slicer. Anyway, perhaps this gets you going:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerCache
    Dim oPt As PivotTable
    For Each oSl In SlicerCaches
        For Each oPt In oSl.PivotTables
            If oPt.Name = Target.Name Then
                MsgBox "The pivot " & oPt.Name & " has " & oSl.Name & " associated with it"
            End If
        Next
    Next
End Sub

 


Comment by: Bob (1/24/2013 9:40:12 AM)

Thanks for the really detailed descriptions on how to work with slicers.

I would like to add one question:
Is there a chance to define which selectable buttons in a slicer are shown? Can you specify a slicer filter to be hidden?
e.g.: My slicer shows 4 options "Products active", "Products inactive", "Products to be developed"
I would like to hide only provide "Products active", "Products inactive" to be selectable.

Best regards
Bob

 


Comment by: Jan Karel Pieterse (1/24/2013 10:19:26 AM)

Hi Bob,

The only way to do that is to have a separate field in the pivot table's source data which would filter out the item you do not want in the slicer. Then you must set up the slicer to hide non-applicable items and filter the pivot table on the field that affects which slicer items apply.

 


Comment by: Bob (1/24/2013 6:51:55 PM)

Hi Jan,

thanks for your quick response.
I tried and it may work, but I cannot tell my slicer to not show non-applicable items.
The only way I could do it is to define the color scheme for items with no data so that the button will become invisible.

 


Comment by: Tiana (2/7/2013 5:34:00 AM)

I need to go through the slicer items one by one and activate each option so I can save the result as a pdf file.

The following code activates each option but when it moves onto the next one it keeps the previous one selected:

Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_cd_merchant_id").SlicerItems
    oSi.Selected = True
    Next
End Sub


Is there a way to select one a time?

Thanks,

T

 


Comment by: Jan Karel Pieterse (2/7/2013 8:33:57 PM)

Hi Tiane,

You could do it like this:
Sub GetSlicerValues()
    Dim oSi As SlicerItem
    For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_cd_merchant_id").SlicerItems
     oSi.Selected = True
     'Now print to PDF
     oSi.Selected = False
    Next
End Sub

 


Comment by: Debbie Leonard (2/21/2013 4:01:32 AM)

I have a button that clears all my slicers filters...it works great, but I'd like to add code to have all slicers scroll back to the top of each slicer list. I've been looking for days but no luck finding an answer. I'm using Slicers with Excel 2010, not PowerPivot.
Thanks.

 


Comment by: Jan Karel Pieterse (2/21/2013 9:55:29 AM)

Hi Debbie,

What you could try is first setting the filter to the first item in the slicer, before removing the filter.

 


Comment by: Debbie Leonard (2/26/2013 1:17:18 AM)

How do I set the filter to the first item in each of my slicers?
Thanks,
Debbie

 


Comment by: Jan Karel Pieterse (2/26/2013 8:52:37 AM)

Hi Debbie,

It took me some digging, but I found a (rather kludgy) way to scroll the slicer to the top. Like so: (note that this also clears the filters)

Sub SetSlicerToFirstItem()
    Dim oActive As Worksheet
    Dim oSi As SlicerItem
    Dim oSc As Slicer
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set oActive = ActiveSheet
    For Each oSh In Worksheets
        oSh.Activate
        For Each oPt In oSh.PivotTables
            For Each oSc In oPt.Slicers
                For Each oSi In oSc.SlicerCache.SlicerItems
                    oSi.Selected = True
                Next
                'Activate a cell
                ActiveCell.Activate
                'Prepare to send some keystrokes
                SendKeys "{TAB}{HOME}"
                'Select the slicer object on the worksheet
                oSh.Shapes(oSc.Caption).Select
                'Now send the keys to that object
                DoEvents
            Next
        Next
    Next
    oActive.Activate
End Sub

 


Comment by: Yan (3/12/2013 6:22:15 AM)

Hi,
What VBA coding can i use to change my pivot table data source that is connected to slicers?

My data will keep increasing and i need to update the data source range every time.


my slicers becomes unreadable when i use the following code:
Sheets("Charts").Activate
    ActiveSheet.PivotTables("PivotTable2").SourceData _
    = Sheets("Data").Range("A7").CurrentRegion.Address(True, True, xlR1C1, True)


and my Excel will crash if i use the following code:
    Sheets("Charts").Activate
    ActiveSheet.PivotTables("PivotTable2").SourceData _
    = Sheets("Data").Range("A7").CurrentRegion.Address(True, True, xlR1C1, True)

ActiveWorkbook.SlicerCaches("Slicer_Geographical_Region").PivotTables(1). _
        PivotCache.Refresh


Thanks

 


Comment by: Jan Karel Pieterse (3/12/2013 8:39:01 AM)
deeplink to this comment

Hi Yan,

No need to keep updating the datasource if you point the pivottable to entire columns, or -even better- if you convert the range to a table and point the Pivot table to the table.

 


Comment by: yan (3/12/2013 11:53:33 AM)
deeplink to this comment

Pointing the Pivot Table to the table works very well!

Many thanks

 


Comment by: Arjo (3/21/2013 11:30:57 AM)
deeplink to this comment

Regarding using vba and slicers: I'd like to protect wy worksheets with slicers to prevent them from being ruined by users. I have used all the security options excel offers but my wanted solution is not available.

My wishes:
-slicers can be used to filter
-slicers can not be edited or deleted.

Given the available options in excel, this is not possible. When my slicers are prevented from deleting (cannot be selected) my slicers don't work anymore: I cannot select another filter option.

how can i achieve the desired security?

 


Comment by: Jan Karel Pieterse (3/21/2013 11:56:18 AM)
deeplink to this comment

Hi Arjo,

You can check the “disable resizing and moving” checkbox in the “position and layout” menu of the slicer and you get exactly the behavior you’re looking for.

 


Comment by: Arjo (3/22/2013 4:56:39 PM)
deeplink to this comment

Sweet! thanks a lot. tried out a lot but not that one.

I think it should be possible to let slicer 1 control pivottable 1 en pivottable 2. (as long as they share the same data source). Is that correct?

I have 2 sheets: pivottable food-drug and pivottable food-drug (2).

1 want to have 1 slicer which controls both. Both pivots are bases on the sheet Food-Drug (2).
So they have exactly the same field names and values.
But when I look at pivottable connections for the slicer, only the pivots at pivottabel food-drug are available to check.

 


Comment by: Jan Karel Pieterse (3/23/2013 7:41:16 PM)
deeplink to this comment

Hi Arjo,

Yes, you can do that.

Check out the section called "Controlling which pivots are handled by your slicers. " on the previous page of this article.

 


Comment by: Alex (3/26/2013 11:07:16 PM)
deeplink to this comment

Hi Jan,

according to Excel 2K10 Help, SlicerItem.Selected is readonly for slicers connected to OLAP data sources.
Is there any workaround beside changing the visiblity of the respective PivotItems of the PivotTable in order to programmatically change the SlicerItem-selection?

I do programmatically change SlicerItem.Selected for standard (non OLAP) pivots and miss this feature quite heavily as we are using more and more OLAP cubes..

Thanks in advance for any idea or comment!

BR Alex

 


Comment by: Jan Karel Pieterse (3/27/2013 3:36:22 PM)
deeplink to this comment

Hi Alex,

In that case I guess all you can do is use the pivotitems visibility indeed, no other option as far as I can see.

 


Comment by: arjo (3/29/2013 10:33:26 AM)
deeplink to this comment

continuing on my question of 3/22/2013: I looked at the mentioned explanation.
this unfortunately didn't answer my question.

Because the pivot table I'd also connect to the slicer is not available in the pivottables that are shown in the list of pivottables.

Both have the same data source...

i have pivot 1 which is connected to food-drug2
and i have pivot 2, which is on another worksheet, which is also connected to food-drug2

but somehow, my pivot2 isn't shown in the available pivots to connect to the slicer.

 


Comment by: Jan Karel Pieterse (3/29/2013 11:49:56 AM)
deeplink to this comment

Hi Arjo,

If 2 pivot tables are connected to the same data source, that does not necessarily mean they share the same pivot cache. You can create pivot tables on the same data source in two distinct ways:

1. By having both tables share the same cache.
2. By giving each PT its own cache.

The first method saves space, as the data source is only loaded once for both tables together.
The second method enables you to have different grouping for each pivot table independently.
If you use the Insert, pivot table button Excel defaults to option 1. If however you use the pivot table wizard, Excel defaults to option 2.
Easiest is to recreate the second PT by using the wizard and selecting Another Pivot table as its source.

 


Comment by: Marco (4/6/2013 3:24:03 AM)
deeplink to this comment

Dear All,

Just a question about a matter that it's not clear for me.
IF I have two different Pivot generated by two differents DB but these Db are some fields in common. If i considere one of these field It's possible to use just one slicer to connect both pivot?
Just to be more clear I make an example:
DB 1:
Fields: A B C (possible values for the field A: 0,1,2)
DB 2:
Fields: A and E (possible values for the field A 0,1,3)
is it possible to use only one slicer to control the field A in both DB (maybe with possible values for A 0,1,2 and 3)or I have to create necessarily two different slicers?
If it's possible also without VBA?
I hope my question is clear
Awaiting for your answer, please let me know
Thanks
Marco

 


Comment by: Jan Karel Pieterse (4/7/2013 4:35:07 PM)
deeplink to this comment

Hi Marco,

No, you cannot. The two pivot tables must use the same pivot cache, which means at least they must use the same source (range).

 


Comment by: JHN (5/30/2013 3:16:07 PM)
deeplink to this comment

Hi,
Trying to control more slicers with different data sources, which works fine when I push selected values from slicer1 to slicer2. It also works when I push selected data from slicer2 to slicer1, except if I have more values in the slicer I'm pushing values to. If there are more sliceritems in slicer they stay visible, but should be turned off.

Any ideas?

Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    
    If mbNoEvents Then Exit Sub
    On Error Resume Next    'IN case a slicer item in slicer 1 is not present in slicer 2
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    '*****Push belt series 1 to 2*****
    If Sh.Name = "Piv BPL" Then
        mbNoEvents = True
        ActiveWorkbook.SlicerCaches("Slicer_Belt_series1").ClearManualFilter
        With SlicerCaches("Slicer_Belt_series")
            For Each oSl In .SlicerItems
                'If SlicerCaches("Slicer_Belt_series1").SlicerItems(oSl.Caption).Selected <> oSl.Selected Then
                    SlicerCaches("Slicer_Belt_series1").SlicerItems(oSl.Caption).Selected = oSl.Selected
                'End If
            Next
        End With
    End If
    
    '*****Push belt series 2 to 1*****
    If Sh.Name = "Piv FL" Then
        mbNoEvents = True
        ActiveWorkbook.SlicerCaches("Slicer_Belt_series").ClearManualFilter
        With SlicerCaches("Slicer_Belt_series1")
            For Each oSl In .SlicerItems
                If SlicerCaches("Slicer_Belt_series").SlicerItems(oSl.Caption).Selected <> oSl.Selected Then
                    SlicerCaches("Slicer_Belt_series").SlicerItems(oSl.Caption).Selected = oSl.Selected
                End If
            Next
        End With
    End If
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

 


Comment by: Jan Karel Pieterse (5/30/2013 4:53:30 PM)
deeplink to this comment

Hi JHN,

All I can come up with is that you must set all sliceritems of the "other" slicer to unselected before you set the slicer items to the same selected value. So two loops per sheet.

 


Comment by: JHN (5/31/2013 10:18:34 AM)
deeplink to this comment

Hi Jan,
Thanks for your feedback. Do you have any suggestions on how to unselect all sliceritems. I tried with:

Dim SC1 As SlicerCache
Set SC1 = ThisWorkbook.SlicerCaches("Slicer_Belt_series")
Dim item1 As SlicerItem
    For Each item1 In SC1.VisibleSlicerItems
        item1.Selected = False
    Next item1

but when the last sliceritem is unselected, all is automatically selected again ... as the slicer can not be "empty"
Any suggestions?
Thanks in advance

 


Comment by: Jan Karel Pieterse (5/31/2013 12:00:56 PM)
deeplink to this comment

Hi JHN,

I'm sorry, I forgot that you need to keep at least one item selected :-(
One way around it is rather than using a for each loop, you use a counted loop starting at item # 2 after making sure item # 1 is selected.
You'll have to adapt the code so that it checks whether Item # 1 actually needs to be selected during the second phase (where you match up the selections of both slicers) and then adjust the selected mode of the item afterwards if necessary.

 


Comment by: JHN (5/31/2013 2:16:27 PM)
deeplink to this comment

Hi Jan,
Any suggestions about how such a code could look like?
thanks in advance

 


Comment by: Jan Karel Pieterse (5/31/2013 4:15:22 PM)
deeplink to this comment

Hi JHN,

Something like this:

    Dim oSl As SlicerItem
    Dim sFirstSlicerItemText As String
    Dim lCt As Long
    Dim bFound As Boolean
    If mbNoEvents Then Exit Sub
    On Error Resume Next    'IN case a slicer item in slicer 1 is not present in slicer 2
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    '*****Push belt series 1 to 2*****
    If Sh.Name = "Sheet" Then
        mbNoEvents = True
        With SlicerCaches("Slicer_Shipcountry2")
            sFirstSlicerItemText = .SlicerItems(1).Name
            .SlicerItems(1).Selected = True
            For lCt = 2 To SlicerCaches("Slicer_Shipcountry2").SlicerItems.Count
                .SlicerItems(lCt).Selected = False
            Next
        End With
        With SlicerCaches("Slicer_Shipcountry")
            For Each oSl In .SlicerItems
                If oSl.Name = sFirstSlicerItemText Then
                    bFound = True
                End If
                SlicerCaches("Slicer_Shipcountry2").SlicerItems(oSl.Caption).Selected = oSl.Selected
            Next
        End With
        If bFound = False Then
            'First slicer item was not in second slicer
            SlicerCaches("Slicer_Shipcountry2").SlicerItems(1).Selected = False
        End If
    End If

    '*****Push belt series 2 to 1*****
    'Exchange both slicer names and change sheet name in if statement......

 


Comment by: JHN (6/5/2013 9:08:16 PM)
deeplink to this comment

Hi Jan, thanks for your feedback ... we are alsomst there (99%). Your script will always leave the first slicer item selected in slicer2, when selecting something in slicer1 which is not present in slicer2. I have spent hours trying to solve it myself without success. Any more ideas?

Would it be possible when selecting an item in slicer1, first checking if it exist in slicer2, if exist sub, if yes run script? ... and how could the checking part look like?

 


Comment by: Hansen (7/15/2013 12:40:47 PM)
deeplink to this comment

Hi there,

I do have multiple slicers all in relation (same source data), and I’m trying to do is following:
Each time one selection is done in a slicer, I would like to count remaining selectable items in each remaining slicers .
I have tried Visiblesliceritems, but that is only working in the slicer where the selection is made, in all other slicers the function will just count all items.

Sub test101()
    Dim SLC1 As SlicerCache
    Dim SLI1 As SlicerItem
    Set SLC1 = ThisWorkbook.SlicerCaches ("Slicer_MBBE_Belt_pitch")
    Range("MBBE_pitch_01") = SLC1.VisibleSlicerItems.Count
    Set SLC1 = ThisWorkbook.SlicerCaches("Slicer_01_MBBE_BPL_MBBE_Belt_series")
    Range("MBBE_series_01") = SLC1.VisibleSlicerItems.Count
End Sub

Any ideas?

 


Comment by: Jan Karel Pieterse (8/13/2013 7:39:30 AM)
deeplink to this comment

Hi Hansen,

Sorry for the belated response!

I expect you need the Selected property of the sliceritem to determine whether the item is filtered or not and count those.

 


Comment by: Rebecca (8/14/2013 6:26:11 PM)
deeplink to this comment

Hi,

Can you tell me if it's possible to link a specific cell to the sliceritem? For example, let's say cell A8 is value "Simon" ... How can I get the slicer to look at cell A8 and automatically filter it on "Simon"? Do I have to call this up in the pivot table vs slicer? If so, how do I do this?

thanks in advance ...

 


Comment by: Jan Karel Pieterse (8/15/2013 8:13:30 AM)
deeplink to this comment

Hi Rebecca,

When the pivot table is created from powerpivot data you can use the CUBE functions to extract what filter was applied. But without VBA I see no way of doing the opposite.

 


Comment by: Jeff Weir (9/6/2013 5:00:11 AM)
deeplink to this comment

Hi Jan Karel. In your diagram, you have the SlicerCache object tied to the pivotCache object.
But what's weird is that given a particular SlicerCache object, there's no direct way you can tell what PivotCache it 'belongs' to, as far as I can tell.

Instead, you have to find out the PivotCache of one of the PivotTables that the slicer is connected to, like this:
? sc.PivotTables(1).PivotCache.Index

...And give a PivotCache object, you can't see what SlicerCaches are 'attached' to a PivotCache, unless you iterate through each PivotTable.

So while a SlicerCache certainly sits under a PivotCache in terms of what a SlicerCache can control, I'd be inclided to put a dotted line in your above diagram, to show that as far as writing VBA code, you can't directly address one via the other.

Your thoughts?

 


Comment by: Jan Karel Pieterse (9/7/2013 4:26:40 PM)
deeplink to this comment

Hi Jeff,

You're probably right, I didn't quite check all my assumptions :-)

 


Comment by: Nicolas Bransier (9/25/2013 1:54:09 AM)
deeplink to this comment

Hi Jan
I'm using a powerpivot and created pivot charts from there. The pivot tables associated to the charts are not visible to me. How can I run a macro each time I change a slicer value.

In your example below, I see that you use Private Sub Workbook_SheetPivotTableUpdate. But in that case, there are no pivot table, only charts.

Thanks in advance

 


Comment by: Jan Karel Pieterse (9/25/2013 9:26:20 AM)
deeplink to this comment

Hi Nicolas,

As far as I know, a pivot chart *allways* has an associated pivottable, regardless whether the pivotchart was added from powerpivot or form Excel.

 


Comment by: Nicolas Bransier (9/25/2013 4:58:12 PM)
deeplink to this comment

Hi Jan
That's what I thought, but how do i identify the name of the pivot table associated to these power charts, they are not visible to me. I aasume all I need is there names so I can use Private Sub Workbook_SheetPivotTableUpdate to implement my code.

 


Comment by: Jan Karel Pieterse (9/25/2013 5:05:15 PM)
deeplink to this comment

Hi Nicolas,

If you put a breakpoint in the event code and then make the pivotchart update it should trigger the event and then you should be able to extract the name of the associated pivot table from the Target object within the event?

 


Comment by: Rick Freitas (9/27/2013 5:09:57 AM)
deeplink to this comment

Thank you again for this site, appreciate you sharing your knowledge with the rest of us! Question I need help with please. I have a date slicer that is connected to multiple pivots (external sources thru powerpivot) and I am trying to vba code that upon opening of workbook sets the date to latest upon refresh of pivots. Code is below:

Private Sub Workbook_Open()
Dim item As SlicerItem
    Dim temp As Date
    temp = Format$("9/25/2013", "d mm yyyy") '' Set the oldest date
    
    ' Find the latest date.
    For Each item In ThisWorkbook.SlicerCaches("Slicer_CalendarDate").SlicerItems
     If item.Name > temp Then
     temp = item.Name
     End If
    Next item
    
     For Each item In ThisWorkbook.SlicerCaches("Slicer_CalendarDate").SlicerItems
     If item.Name = temp Then '' Compare item
        item.Selected = True
        Else
        item.Selected = False
     End If
    Next item
    
ThisWorkbook.RefreshAll

End Sub

So issue is I am getting "run-time error '1004': application-defined or object defined error" on the For Each line right below the commented "' Find the latest date." line (line 6). I know I am using right slicer name as due to going to slicer settings and seeing 'name to use in formulas' says "Slicer_CalendarDate", but if I run the code in a previous post above on getting slicer values, for slicer name it says : [SalesDateViewMonth].[CalendarDate].[CalendarDate].

I have tried using that name as well, but still gives an error, maybe not using right syntax. Anyways, any thoughts you have on what might be going on would be greatly appreciated. Most likely I am missing something simple, I am not much of a VBA coder, but trying. :)

Thank You!

Rick

 


Comment by: Jan Karel Pieterse (9/27/2013 11:15:00 AM)
deeplink to this comment

Hi Rick,

Try if moving the code to a sub in a normal module works.
If it does, put a call tot that sub in Workbook_Open, preferably by using:

Private Sub Workbook_Open()
    Application.Ontime Now, "NewSubsName"
End Sub

 


Comment by: JHN (10/14/2013 3:24:24 PM)
deeplink to this comment

Hi,
I have been trying to write a macro which count sliceritems left with data.
Ex: Slicer1, Slicer2 and Slicer3 all contains 10 items, and by selecting 1 item in slicer1, only 2 items are left in slicer2 and 5 items are left in slicer3.
I now want to count the numbers of items in slicer2 and slicer3 and write the result in cell B1 and C1 for later use.
Any suggestions how a code could look like?

JHN

 


Comment by: Jan Karel Pieterse (10/15/2013 10:09:20 AM)
deeplink to this comment

Hi JHN,

This function returns a list of selected items for a slicer passed to the function. I assume you can modify this to fit your needs:

Public Function GetSelectedItemsOfSlicer(oSl As Slicer) As Variant
    Dim vSelected() As Variant
    Dim oSi As SlicerItem
    Dim lCt As Long
    ReDim vSelected(1 To 1)
    For Each oSi In oSl.SlicerCache.SlicerItems
        If oSi.Selected Then
            lCt = lCt + 1
            ReDim Preserve vSelected(1 To lCt)
            vSelected(lCt) = oSi.Value
        End If
    Next
    GetSelectedItemsOfSlicer = vSelected
End Function

 


Comment by: Arjo (11/18/2013 9:49:03 AM)
deeplink to this comment

I've use the first sub (to enumerate all the slicer caches in a workbook) to find all the slicer caches.

Nothing happens though when I execute the sub.

Í'm not able to connect slicers to any pivot So i wonder what I have to do to change the slicer caches to the same sourche as the pivots. (to be able to connect the slicers again.

 


Comment by: Jan Karel Pieterse (11/18/2013 1:13:15 PM)
deeplink to this comment

Hi Arjo,

You mean it doesn't even show any message boxes?

 


Comment by: Arjo (11/18/2013 1:46:58 PM)
deeplink to this comment

nope. i think there was something wrong with the file.
I've copied another file to test and used the adjusted code below to find the names of the caches and their location:

Sub MultiplePivotSlicerCaches()

    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As pivotTable
    Dim oSh As Worksheet
    
    Set objnewsheet = worksheets.Add
    objnewsheet.Activate

iRow = 1



    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.PivotTables
            objnewsheet.Cells(iRow, 1) = oSlicercache.Name & ", " & oPT.Name & ", " & oPT.Parent.Name
            iRow = iRow + 1
        Next
    Next


End Sub


the first time, I get a nice complete list of all the caches and the related pivots:

after that, I ran a code to update the data and to update all the pivots etc.

After that, I ran the same code again and then I only get the results for half of the slicers (only the slicers prod and merk

Slicer_PROD, Draaitabel1, draaitabel food-drug (4)
Slicer_PROD, Draaitabel3, draaitabel food-drug (4)
Slicer_PROD, Draaitabel4, draaitabel food-drug (4)
Slicer_PROD, Draaitabel5, draaitabel food-drug (4)
Slicer_MERK, Draaitabel1, draaitabel food-drug (3)
Slicer_MERK, Draaitabel1, draaitabel food-drug (2)
Slicer_MERK, Draaitabel3, draaitabel food-drug (2)
Slicer_MERK, Draaitabel4, draaitabel food-drug (2)
Slicer_MERK, Draaitabel5, draaitabel food-drug (2)
Slicer_MERK, Draaitabel3, draaitabel food-drug (3)
Slicer_MERK, Draaitabel4, draaitabel food-drug (3)
Slicer_MERK, Draaitabel5, draaitabel food-drug (3)
Slicer_MERK, Draaitabel1, draaitabel food-drug (4)
Slicer_MERK, Draaitabel3, draaitabel food-drug (4)
Slicer_MERK, Draaitabel4, draaitabel food-drug (4)
Slicer_MERK, Draaitabel5, draaitabel food-drug (4)


my updating scripts seems to have problems with the slicers MKT or FCT1

 


Comment by: Arjo (11/29/2013 11:48:33 AM)
deeplink to this comment

Hello,

does the slicer also have the option (in vba) to be Always scrolled to the top of the slicer?

i'd like to do this for the slicers I use

 


Comment by: Jan Karel Pieterse (11/29/2013 5:32:40 PM)
deeplink to this comment

Hi Arjo,

Click on this link and look for a sub named "SetSlicerToFirstItem":

http://www.jkp-ads.com/Articles/slicers03.asp?AllComments=True

 


Comment by: Sarah (12/18/2013 10:53:31 PM)
deeplink to this comment

I tried using the code above to set the slicer to the first item, but I'm not able to get it to work...

I have the macro assigned to a command button, but when I click on it, I get this error code:

Run-time error '-2147024809 (80070057)':
The item with the specified name wasn't found

I'm so lost and would gladly welcome your help! Below is the code that had been previously posted in the comments.

Private Sub CommandButton1_Click()

Dim WSActive As Worksheet
Dim oSi As SlicerItem
Dim oSc As Slicer
Dim oPt As PivotTable
Dim oSh As Worksheet
    
Set WSActive = ActiveSheet
    For Each oSh In Worksheets
        oSh.Activate
        For Each oPt In oSh.PivotTables
            For Each oSc In oPt.Slicers
                For Each oSi In oSc.SlicerCache.SlicerItems
                    oSi.Selected = True
                Next
                'Activate a cell
                ActiveCell.Activate
                'Prepare to send some keystrokes
                SendKeys "{TAB}{HOME}"
                'Select the slicer object on the worksheet
                oSh.Shapes(oSc.Caption).Select
                'Now send the keys to that object
                DoEvents
            Next
        Next
    Next
    oActive.Activate

End Sub


 


Comment by: Jan Karel Pieterse (12/19/2013 9:47:54 AM)
deeplink to this comment

Hi Sarah,

No idea what is wrong. Have you refreshed your pivot table?

 


Comment by: Megan (2/6/2014 5:44:46 PM)
deeplink to this comment

Hello,
I am trying to run a macro when a slicer is selected (or my pivot table associated with the slicer is filtered) and I am new with VBA. I thought the code you gave someone above was similar to what I needed but when I used it and modified it for my workbook, nothing happens. Basically, I need a macro that causes a different pivot table to filter, once I filter with the first pivot table providing the illusion that both pivot tables are linked to the one slicer. Can you tell me what I am doing wrong or help me write a better solution?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iSect As Range
    Set iSect = Application.Intersect(Target, Range("Pivot_PropertyNames"))
    If Not iSect Is Nothing Then
        Call FilterPivotTable
    End If
End Sub

Sub FilterPivotTable()
Application.ScreenUpdating = False
    Sheets("DailyProduction").Range("I1") = Sheets("PIVOT TABLE").Range("B1").Value
Application.ScreenUpdating = True
End Sub

 


Comment by: Jan Karel Pieterse (2/6/2014 7:30:03 PM)
deeplink to this comment

Hi Megan,

I'm not sure what your macro si supposed to do. Is it trying to change the value of a cell that is in fact a pivot page field? That is not how you change a pivot filter.
There is code on this page for synching two pivot tables, click http://www.jkp-ads.com/Articles/slicers03.asp?AllComments=True and look for it.

 


Comment by: praveen (3/21/2014 1:27:19 PM)
deeplink to this comment

Hi Folks,

how to deselect slicer items without using loops. i want to deselect at a time all slicer items.
If anybody know, please help me

thanks for advanced.

 


Comment by: abdul1987 (4/7/2014 2:43:21 PM)
deeplink to this comment

how to deselect and select only required items form slicer through VBa code

 


Comment by: Jan Karel Pieterse (4/7/2014 3:15:50 PM)
deeplink to this comment

Hi Abdul1987,

The next page shows how to select items in a slicer, perhaps it gives you enough information to solve your problem?

http://www.jkp-ads.com/Articles/slicers04.asp

 


Comment by: Paul Ranschaert (4/24/2014 5:40:06 PM)
deeplink to this comment

Hi, I've created a dashboard with 4 different line graphs showing performance in the last 24-36 months. Every pivotchart is connected with a slicer. The slicers contain about 100 different kpi's. If for example I would like to review the performance for perfect customer orders I select pco and the underlying kpis being the performance on on time deliveries, in full deliveries and availabilty. I also have other combinations but this takes very long every time I need to scroll down the slicers. To make it easier for the users I would like to add macro buttons with pre-defined combinations. I've recorded a macro but when I run the macro it takes ages because the pivottable is re-freshed for every kpi in the slicer. Does somebody have a solution to speed up this process?
Thanks, Paul


Sub PCO_2()
'
' PCO_2 Macro
'

'
    With ActiveWorkbook.SlicerCaches("Slicer_KPI1")
        .SlicerItems("PCO (Perfect customer Order)").Selected = True
        .SlicerItems("Accident Frequency").Selected = False
        .SlicerItems("Actual traded (hl)").Selected = False
        .SlicerItems("Annual Personnel Expense / SC FTE").Selected = False
        .SlicerItems("Asset Turnover (COGS)").Selected = False
        .SlicerItems("Asset Turnover (Revenue)").Selected = False
        .SlicerItems("Asset Utilization").Selected = False
        .SlicerItems("Asset Utilization Bottling").Selected = False
        .SlicerItems("Asset Utilization Brewhouse").Selected = False
        .SlicerItems("Asset Utilization Canning").Selected = False
        .SlicerItems("Asset Utilization Kegging").Selected = False
    
    End With
    
End Sub

 


Comment by: Jan Karel Pieterse (4/28/2014 10:06:59 AM)
deeplink to this comment

Hi Paul,

Does it help to add this as the first line of your macro:

Application.ScreenUpdating = False

 


Comment by: Paul Ranschaert (4/28/2014 1:22:13 PM)
deeplink to this comment

Hi Jan Karel,

thanks it helps but it is still slow, with the above mentioned code it takes about 45 seconds and without it takes about 75 seconds. When I run it without the code, I see the following messages: Running Slicer Operations and Calculating Pivottable for every slicer item. Probably with a couple of slicer items this would be a good solution but with the number of items I have it's too slow.

 


Comment by: Jan Karel Pieterse (5/8/2014 8:36:11 AM)
deeplink to this comment

Hi Paul,

What if you turn the PivotTable's ManualUpdate to True in your code?

 


Comment by: Kuldip Mond (6/20/2014 11:17:46 AM)
deeplink to this comment

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
                        Next
                    Next
                End If
oPT.Parent.Name
            End If
        Next
    Next
End Sub

 


Comment by: Jan Karel Pieterse (6/20/2014 11:42:49 AM)
deeplink to this comment

Hi Kuldip,

You declared oS1 as SlicerCacheLevel, but the for loop runs through the SlicerCaches collection, thus requiring an object variable of type SlicerCache.

 


Comment by: Kuldip Mond (6/20/2014 12:03:24 PM)
deeplink to this comment

Still confused, so how should that line of code read ? or do I need to redesign the entire loop. I don't think I am fgar off but don't think I have the ncessary knowledge to fix this. I am trying hard though :-(

Thanks in Advance Kuldip

 


Comment by: Jan Karel Pieterse (6/20/2014 1:23:28 PM)
deeplink to this comment

Hi,

Simply replace

Dim oSl As SlicerCacheLevel

with

Dim oSl As SlicerCache

 


Comment by: Zachary Bass (7/2/2014 5:55:02 AM)
deeplink to this comment

Hi There:

Is there any way to speed up the slicer selection below. I have a very small pivot table with about 200 slicer items, but it takes along time to update the pivot table as the code manages each slicer item. Thanks for any help. Zachary


Sub TestSlicer()
    Call ChangeSlicer("Slicer_ID_NAME", "000001_")
End Sub

Sub ChangeSlicer(scache1 As String, sname1 As String)
    
    Dim i As Long
    Dim cache1 As SlicerCache
    Dim sItem As SlicerItem

    Application_Off

    With ActiveWorkbook
        '// Clear and set individual slicers
        .SlicerCaches(scache1).ClearManualFilter
        Set cache1 = .SlicerCaches(scache1)
        For Each sItem In cache1.SlicerItems
            If sItem.Name <> sname1 Then
                sItem.Selected = False
            End If
        Next
    End With
    Application_On
End Sub

Public Sub Application_Off()
    With Application
        .StatusBar = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
End Sub

Public Sub Application_On()
    With Application
        .StatusBar = False
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

 


Comment by: Jan Karel Pieterse (7/3/2014 8:07:49 AM)
deeplink to this comment

Hi Zachary,

I'm afraid I could not find a way to speed that up!

 


Comment by: Rafael Lepra (7/14/2014 9:58:06 PM)
deeplink to this comment

Is it possible to unselect all the SlicerItem for a particualr Slicer? I tried to do it but it seem that there is a requirement to have at least one item selected, because as soon as the macro sets the Selected property of the last item to False, all the slicer items are selected again. Is there a way to turn off this requirement? I searched but I sould not find a way to do it. Even if you go to the drop down filter on the pivot table, if you unselect all, the OK button is greyed out.
Thank you in advance for your help.

 


Comment by: XQR (7/15/2014 2:39:21 AM)
deeplink to this comment

Jan:
I want to show the text(such as tooltips) by selecting the slicer member which is a number.
I have a lookup list(less than 100), layout as number, text (80 Health).
How I can show the "Health" when I hover into "80".
Please have a piece of sample code and let me know where the code should be saved into.
Thanks in Advance.

 


Comment by: Jan Karel Pieterse (7/16/2014 4:32:39 PM)
deeplink to this comment

Hi XQR,

Please ask your question here:
www.eileenslounge.com

 


Comment by: Jan Karel Pieterse (7/16/2014 4:34:14 PM)
deeplink to this comment

Hi Rafael,

No, you cannot unselect all items, just as you cannot uncheck all items in the usual pivot filter.

 


Comment by: Marian (11/14/2014 11:06:26 PM)
deeplink to this comment

Stranded slicer caches?

To summarize the long story below: Did I strand some old slicer caches that I should somehow have removed when I changed pivot caches with VBA? If so, how do I remove them? Is there a better way to do what I need to do?

I have a workbook with multiple pivot tables and slicers. The workbook pulls data from MS CRM on line, using a spreadsheet that connects to CRM via FetchXml. That means that my pivot source is 'Sheet X'!Query_from_Microsoft_CRM. The query changes every month or two, although the columns returned always have the same names. I cannot edit the query from Excel--I have to make a new FetchXML query in CRM, pull the results into an Excel sheet as a dynamic table, attach the new query sheet to my workbook, disconnect slicers that are connected to multiple pivots, change the pivot data sources, and reconnect the slicers.

I know just enough VBA to be dangerous, and am trying to automate the above. I've copied and use macros from this site and Contextures for listing pivots,pivot source data, pivot cache indexes, slicer caches, and pivot tables connected to slicer caches. Today, I disconnected the slicers, changed one pivot cache manually, noted the new cache index, and used approximately the following to change the pivot caches
    For Each pt In wks.PivotTables
             pt.CacheIndex=2
        Next pt

Then I manually reconnected the slicers to the appropriate pivot tables. This seemed to work, but later Excel crashed. The error log had errors beginning with
error212080_01.xml, and continuing with
Removed Records: Slicer Cache from /xl/slicerCaches/slicerCache10.bin part (Slicer Cache)or with
Removed Records: Slicers from /xl/slicers/slicer1.bin part (Slicer)
or
Removed Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape).

All the errors ended with
Repaired Records: Named range from /xl/workbook.bin part (Workbook).

Thank you,
Marian

 


Comment by: Jan Karel Pieterse (11/17/2014 9:38:00 AM)
deeplink to this comment

Hi Marian,

In your specific case I'd be inclined to reconsider the design. I would suggest to use a separate workbook which pulls the data from the CRM system. Then in your report workbook simply use a static table as a source for the pivottables. If new data is needed, you empty that static table and paste the new data from the connected workbook into the report workbook.
Doing that avoids having to do all the reconnecting of slicers and PT's.

 


Comment by: Brad (11/18/2014 1:14:15 PM)
deeplink to this comment

Hey,

I am looking for a macro to be able to scroll through my slicer options (all of them ~ 50 options) and print each of the player reports that are associated with it. I have looked everywhere for one like this but neither understand it properly or can't find one that works with the slicer..

If anyone could help me out with a code that would be AMAZING and save so much time!!


Thankyou

 


Comment by: Jan Karel Pieterse (11/18/2014 3:10:50 PM)
deeplink to this comment

Hi Marian,

You could use code like this:

Sub SelectEachItemOfSlicer()
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim oSi1 As SlicerItem
    Set oSc = ActiveWorkbook.SlicerCaches("Slicer_ShipCountry")
    For Each oSi In oSc.SlicerItems
        'First clear all filters
        oSc.ClearManualFilter
        'Only do this for slicer items that currently have data
        If oSi.HasData Then
            'Now only show the current one
            For Each oSi1 In oSc.SlicerItems
                If oSi.Name = oSi1.Name Then
                    oSi1.Selected = True
                Else
                    oSi1.Selected = False
                End If
            Next
            'Now print, or do anything else
        End If
    Next
    oSc.ClearManualFilter
End Sub

 


Comment by: Xolani (2/20/2015 8:43:54 AM)
deeplink to this comment

Hi guys.
Can anyone help me here.. I have two pivotTables from different sources, however I would like to control both of them using only one slicer. Is there anyone who could help with the VBA code that connects two slicers

 


Comment by: Jan Karel Pieterse (2/21/2015 4:19:59 PM)
deeplink to this comment

Hi Xolani,

There are some examples on this page:

http://www.jkp-ads.com/Articles/slicers04.asp

 


Comment by: Stuart Dunlap (8/6/2015 8:56:48 PM)
deeplink to this comment

Hi Jan,

Thanks for this terrific summary! I'm hoping you can give some insight on how to improve performance for a macro I have.

I have six slicers attached to five PivotTables in an Excel 2010 workbook. The data source for the PivotTables is a SSAS OLAP cube. I'm using the following code to update the Pivot Slicer parameters:

Sub SliceFilter_State()
Dim i As Long
Dim rg As Range, cel As Range
Dim vaFilter() As Variant

Set rg = cpivParms.Range("rgState")

i = 1

For Each cel In rg
    If cel.Offset(0, 2).Value = 1 Then
        ReDim Preserve vaFilter(1 To i)
        vaFilter(i) = "[State].[State].&[" & cel.Value & "]"
        i = i + 1
    End If
Next cel

ThisWorkbook.SlicerCaches("Slicer_State").VisibleSlicerItemsList = vaFilter()

End Sub


Here's my problem... Performance to run the macro that updates the Slicers is extremely slow. I tried preceding all Slicer updates with the following code:

Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName <> "cpivParms" Then
        For Each pt In ws.PivotTables
            pt.ManualUpdate = True
        Next pt
    End If
Next ws


After the Slicer parameters have been selected, I then set pt.ManualUpdate to False. However, changing the ManualUpdate property does not stop a query refresh after a Slicer update and it's the query refreshes after each of the five Slicer updates that's eating time away.

Is there anyway in Excel 2010 to turn off query refresh until after all Slicers have been updated?

I appreciate any insight!

 


Comment by: Jan Karel Pieterse (8/7/2015 2:17:00 PM)
deeplink to this comment

Hi Stuart,

Hmm, I would not expect that changing a slicer would trigger a query update to begin with. Is the pivottable in question set to not save data with it? Does a manual slicer selection trigger the query refresh as well?

 


Comment by: Stuart Dunlap (8/7/2015 2:52:03 PM)
deeplink to this comment

Jan - thank you for the post.

To answer your first question ... this PivotTable is connected to a SSAS OLAP cube, so I don't have the option of saving the data with it. Under the Data tab of the PivotTable Options dialog, the button 'Save source data with file' is grayed out.

To answer your second question, I did a manual slicer selection change and that did trigger a query refresh. In fact, it generated multiple query refreshes - bonus! :-)
(I'm saying it triggered query refreshes because the lower right section of the status bar said 'Running OLAP query ... (Press Esc to Cancel)' and this message flashed five times, I'm guessing one flash for each PivotTable.)

I did some research after my post yesterday and I happened across the following site that would seem to suggest there is no way to control the query refresh in Excel 2010:

https://social.msdn.microsoft.com/Forums/office/en-US/4f28c068-150b-4aa1-bf04-3fdb2bac4b1a/olap-cube-pivot-table-update-refresh-on-slicer-selection-changes?forum=exceldev

However, after doing the manual update to answer your second question, I'm wondering if there would be a way through VBA to 'Hit the Escape' key to prevent the refresh, and then do one last refresh after I've updated all my Slicers.

Do you know of a way to 'Hit the Escape key' with VBA?

 


Comment by: Jan Karel Pieterse (8/10/2015 10:47:04 AM)
deeplink to this comment

Hi Stuart,

Well, you can send a key to Excel:

SENDKEYS "{ESC}"
DoEvents
'Now change the slicer

but I seriously doubt that will fix the problem as you will have no control over when the key is "accepted by Excel".

 


Comment by: Sabrina (8/25/2015 8:13:52 PM)
deeplink to this comment

I am attempting to update (sort by columns) an Excel table based on a slicer. While the vba code will work; it will only update after I make a selection if I run the actual macro. However, I need for the table to update every time I make a selection from the slicer. What am I missing?

Sub UpdateTSM()

Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Integer
Dim oneRange As Range
Dim aCell As Range

Set sc = ActiveWorkbook.SlicerCaches("Slicer_market")
Set oneRange = Range("Table6")
Set aCell = Range("I10")

     For Each si In sc.SlicerItems
     If si.Selected = True Then
     oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
            End If
        Next si

End Sub

 


Comment by: Jan Karel Pieterse (8/26/2015 11:24:57 AM)
deeplink to this comment

Hi Sabrina,

Unfortunately, Table slicers trigger no specific event like slicers on Pivottables have (the Workbook_SheetPivotTableUpdate in the ThisWorkbook module).

So all you can do is use the Change event of the worksheet in question and add some logic that detects whether the changed cell is within the table the slicer is driving.
From that event you can call your current macro.

 


Comment by: GuyC (12/10/2015 3:21:02 PM)
deeplink to this comment

Your blog has helped HUGELY to get me to a point where I can interrogate SlicerItems and iterate through them.

I want to cycle through the slicer items, select one item (1=True, others = False), perform an action, then move on to the next.

I have the code below, which works nicely (I've had Debug.Print's in there to show values etc as it run, and I can see the SlicerItem values are found and being compared in the IF/THEN). However... I get the old error "Run-time error 1004: Application-defined or object-defined error" when it reaches line>> oSi.Selected = True

Any ideas?
How do I set the Selected value (True/False) for the active SlicerItem (oSi or oSi1)?


Sub GetSlicerValues3()
    Dim oSi As SlicerItem
    Dim oSi1 As SlicerItem
    Dim oSl As SlicerCacheLevel
    Set oSc = ActiveWorkbook.SlicerCaches("Slicer_IMT")
    For Each oSl In ActiveWorkbook.SlicerCaches("Slicer_IMT").SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
         'For Each oSi In oSc.SlicerItems
                'First clear all filters
                oSc.ClearManualFilter
                ' Only do this for slicer items that currently have data
                If oSi.HasData Then
                    'Now only show the current one
                    For Each oSi1 In oSl.SlicerItems
                        If oSi.Value = oSi1.Value Then
                            oSi.Selected = True
                        Else
                            oSi.Selected = False
                        End If
                    Next
                    'Now do something
                End If
            'Next
        Next
    Next
End Sub

 


Comment by: Jan Karel Pieterse (12/10/2015 10:56:59 PM)
deeplink to this comment

Hi Guy,

Nothing appears to be wrong with your code, perhaps something I am overlooking. Can you email the file to me perhaps?

 


Comment by: sathish Kumar (1/5/2016 1:30:25 PM)
deeplink to this comment

Set Data_sht = ThisWorkbook.Worksheets("Sheet5")
Set Pivot_sht = ThisWorkbook.Worksheets("Sheet12")
Set Pivot_sht1 = ThisWorkbook.Worksheets("Sheet11")
Set Pivot_sht2 = ThisWorkbook.Worksheets("Sheet13")


PivotName = "PivotTable4"
PivotName1 = "PivotTable3"
PivotName2 = "PivotTable5"

Set StartPoint = Data_sht.Range("A1")
Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
     & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
End If

Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
    
    Pivot_sht1.PivotTables(PivotName1).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
     Pivot_sht2.PivotTables(PivotName2).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
Pivot_sht.PivotTables(PivotName).RefreshTable
Pivot_sht1.PivotTables(PivotName1).RefreshTable
    Pivot_sht2.PivotTables(PivotName2).RefreshTable

I am using the above code to update source data for 3 pivot tables. The code is getting executed only when i uncheck the pivot table connections in the slicer properties. Kindly suggest a way to execute this

 


Comment by: Jan Karel Pieterse (1/6/2016 4:10:03 PM)
deeplink to this comment

Hi sathish,

What error do you get precisely?

 


Comment by: Alejandro Alarcon (1/11/2016 8:03:58 AM)
deeplink to this comment

i need some help plz! I'm trying to do a macro that create a slicer for ANY table not only one in specific but it only works for a specific table because the macro reads the table's name and use it through the whole macro. this is the maro and you can see that it says "tabla2463", is there any way to generalize this macro so it works for any table?

Sub test()
'
' Macro create a slicer for a table
'
    Range("A2").Select
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Tabla2463"), _
        "COMM PERCENTAGE").Slicers.Add ActiveSheet, , "COMM PERCENTAGE", _
        "COMM PERCENTAGE", 129, 519.75, 144, 198.75
    ActiveSheet.Shapes.Range(Array("COMM PERCENTAGE")).Select
    With ActiveWorkbook.SlicerCaches("SegmentaciónDeDatos_COMM_PERCENTAGE")
        .SlicerItems("6").Selected = True
        .SlicerItems("1.5").Selected = False
    End With

 


Comment by: Jan Karel Pieterse (1/11/2016 1:18:46 PM)
deeplink to this comment

Hi Alejandro,

You could replace this:

ActiveSheet.ListObjects("Tabla2463")

with

ActiveCell.ListObject

 


Comment by: Eugene (1/29/2016 10:04:05 AM)
deeplink to this comment

Hi Jan Karel Pieterse

I need to select first slicer item that currently have data.
But my pivot table is getting data from an external source.
Could you please share an example how to do that?

Beacause my code is same as GuyC and I got same error(1004).

Best regards,
Eugene

 


Comment by: Jan Karel Pieterse (1/29/2016 2:28:55 PM)
deeplink to this comment

Hi Eugene,

It is probably something like this (not tested):

Sub JKPv2()
    Dim oSi As SlicerItem
    Dim oSl As SlicerCacheLevel
    Set oSc = ActiveWorkbook.SlicerCaches("Slicer_IOT")
    For Each oSl In oSc.SlicerCacheLevels
        For Each oSi In oSl.SlicerItems
            If oSi.HasData Then
                oSc.VisibleSlicerItemsList = Array("[TableName].[SlicerName].&[" & oSi.Value & "]")
            End If
        Next
    Next
End Sub

 


Comment by: Raja (2/22/2016 10:29:45 AM)
deeplink to this comment

when I update the Pivot table sourec by the below code it gets updated but the Slicer is not updating. Please hlep.

ActiveWorkbook.Worksheets("SUMMARY").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="named range", _
Version:=xlPivotTableVersion10)

 


Comment by: Jan Karel Pieterse (2/22/2016 11:14:22 AM)
deeplink to this comment

Hi Raja,

What happens precisely?

 


Comment by: Rakesh (3/7/2016 10:27:34 AM)
deeplink to this comment

I am looking for macro which can change the pivot table filed based on silcer selection.

For example I have three silcer Options L1/L2/L3 and if I select any time in L1 Sicer cach then pivot table filed should changed and if I clear filter on L1 slicer caches then again it should bring back with original filed so bacially I want to show the info as per slicers selection
Hope you understand my query

Thanks in advance for your support

 


Comment by: kschaefer (3/10/2016 11:16:34 PM)
deeplink to this comment

is there anyway to reset the slicers to show all after manually changing the selection.

I have tried ClearManualFilter and it seems to be buggy.

K

 


Comment by: Jan Karel Pieterse (3/11/2016 10:30:18 AM)
deeplink to this comment

Hi Rakesh,

I'm afraid I don't understand what you need, sorry! Can you try to rephrase the question? Alternatively, go to http://www.eileenslounge.com and ask your question there.

 


Comment by: Debbie (4/14/2016 12:02:21 AM)
deeplink to this comment

I have two slicers on my picot table for enrollment. One for Department and one for Course#. By nature of the sheer number of courses that we have the slicer is only showing a portion of them at a time. I have the slicer settings as "Show items with no data last".

When a user scrolls down looking at a long list of courses for a particular department the scrollbar stays in that position. When they then select another department with say fewer course selection in the Course# slicer they only see the greyed out ones and have to manually move the scrollbar up to the top. Many users do not figure this out for some reason.

I want the course slicer's scrollbar to move to the top anytime a selection is made in the department slicer.

Debbie

 


Comment by: Jan Karel Pieterse (4/14/2016 10:11:19 AM)
deeplink to this comment

Hi Debbie,

Perhaps you can use (part of) this code. It is a bit cluncky but it seems to work:

Sub SetSlicerToFirstItem()
    Dim oActive As Worksheet
    Dim oSi As SlicerItem
    Dim oSc As Slicer
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set oActive = ActiveSheet
    oSc.SlicerCache.ClearManualFilter
    For Each oSh In Worksheets
        oSh.Activate
        For Each oPt In oSh.PivotTables
            For Each oSc In oPt.Slicers
                'Remove filter, perhaps you might want to leave this out
                For Each oSi In oSc.SlicerCache.SlicerItems
                    oSi.Selected = True
                Next
                'Activate a cell
                ActiveCell.Activate
                'Prepare to send some keystrokes
                SendKeys "{TAB}{HOME}"
                'Select the slicer object on the worksheet
                oSh.Shapes(oSc.Caption).Select
                'Now send the keys to that object
                DoEvents
            Next
        Next
    Next
    oActive.Activate
End Sub

 


Comment by: SCherian (5/5/2016 10:05:47 PM)
deeplink to this comment

I read your blog and many people posted the same question and I don't find a solution in any of the post. Here is my problem. I'm trying to select slicer items based on a value in drop down. I tried so many different ways but I'm getting "application defined or object defined error". Slicers on my sheet from multiple sources. So I'm using SlicerCacheLevels as you suggested in your blog. I'm able to get the values. But when I try to set sI.Selected = False/True I'm getting this error. Could you please tell what I might be doing wrong. I appreciate any help.


<VB>
Sub Step_Thru_SlicerItems(ByVal SlicerName As String)
    Dim sC As SlicerCache
    Dim sI As SlicerItem
    
    Set sC = ActiveWorkbook.SlicerCaches(SlicerName)
    
    Dim c As Range
    Dim Rng As Range
    Set Rng = Range("ActivityMonths")
    For Each c In Rng
        For Index = 1 To sCL.Count
            For Each sI In sC.SlicerCacheLevels(Index).SlicerItems
                'On Error GoTo errHandler
                Debug.Print "Slicer Item : " & sI.Value & " Selected : "; sI.Selected
                sI.Selected = False
            Next sI
        Next Index
    Next c
    
errHandler:
    Application.EnableEvents = True
    Exit Sub
    
End Sub
</VB>

 


Comment by: Jan Karel Pieterse (5/11/2016 3:55:09 PM)
deeplink to this comment

Hi SCherian,

Sorry for the belated reply. Look for a post by me dated 1/29/2016 2:28:55 PM, it may contain just the code snippet you need.

 


Comment by: SCherian (5/11/2016 4:35:28 PM)
deeplink to this comment

Hi Jan,

I have modified the code based on your 1/29/2016 2:28:55 PM post. But I'm getting the same error "application defined or object defined error" as mentioned in my previous post. Please help.

<VB>
Sub Step_Thru_SlicerItems(ByVal SlicerName As String)
    Dim sC As SlicerCache
    Dim sI As SlicerItem
    Dim sCL As SlicerCacheLevel
    
    Set sC = ActiveWorkbook.SlicerCaches(SlicerName
    
    Dim c As Range
    Dim Rng As Range
    Set Rng = Range("ActivityMonths")

For Each sCL In sC.SlicerCacheLevels
For Each sI In sCL.SlicerItems
If sI.HasData Then
Debug.Print "Slicer Item : " & sI.Value & " Selected : "; sI.Selected
sI.Selected = False
End If
Next sI
Next sCL
    
End Sub
</VB>
Thanks,
SCherian

 


Comment by: Jan Karel Pieterse (5/11/2016 5:33:05 PM)
deeplink to this comment

HI SCherian,

The code you post does not resemble the one I refer to, especially the part that actiually does the selecting:

oSc.VisibleSlicerItemsList = Array("[TableName].[SlicerName].&[" & oSi.Value & "]")

 


Comment by: SCherian (5/11/2016 6:41:17 PM)
deeplink to this comment

Hi Jan,

I wanted to select multiple items in the slicer list based on a drop down value.

Yes, with the code <vb>oSc.VisibleSlicerItemsList = Array("[TableName].[SlicerName].&[" & oSi.Value & "]") </vb> I was able to select only one item.

Is there a way to select multiple items.

Thanks,
Scherian

 


Comment by: SCherian (5/12/2016 12:29:50 AM)
deeplink to this comment

Hi Jan,
Thank you so much for the help. You shed light to solve my problem. In order to select multiple items in a slicer all I have to do is add those items that I have to multi-select into an array and assign that Array to the VisibleSlicerItemsList. I'm posting the code here that worked for here coz it may help someone with the same usecase. I'm passing slicer name and the name of the named range (the values that needs to be set in the slicer is stored in a namedrange cells. These values are coming from the database based on a selection criteria). The better approach in the logic would be, instead of looping through the slicer items, construct the array by looping through the values arraylist(i) = "[TableName].[SlicerName].&[" & value(i+1) & "]" and set the VisibleSlicerItemsList = arraylist.

Here is the code snippet that worked for me:


<VB>
Sub UpdateSlicer(ByVal SlicerName As String, ByVal RangeName As String)
    Dim sC As SlicerCache
    Dim sI As SlicerItem
    Dim sCL As SlicerCacheLevel
    
    Set sC = ActiveWorkbook.SlicerCaches(SlicerName)
    
    Dim c As Range
    Dim Rng As Range
    Set Rng = Range(RangeName)
    
    
    Dim monthList() As Variant
    Dim Index As Integer
    Index = 0
    ReDim monthList(0 To (Rng.count - 1))
    
    
    On Error GoTo errHandler
    
    For i = 1 To sC.SlicerCacheLevels.count
        For Each sI In sC.SlicerCacheLevels(i).SlicerItems
            'Debug.Print "Slicer Item Name :" & sI.Name
            For Each c In Rng
                If sI.Value = c Then
                    monthList(Index) = sI.Name
                    Index = Index + 1
                End If
            Next
        Next
    Next i
    sC.VisibleSlicerItemsList = monthList
    
errHandler:
    Application_On
    Exit Sub
    
End Sub
</VB>

 


Comment by: Jan Karel Pieterse (5/12/2016 7:40:26 AM)
deeplink to this comment

Hi SCherian,

Yes that would've been my answer too. Good work finding the answer!

 


Comment by: Anna (6/23/2016 7:07:26 PM)
deeplink to this comment

Hello, I trying to do macro for slicer in pivot table. If I choose any store name other rows should hide.
Sub Store()
Dim wb As Workbook
Dim sc As SlicerCache
Dim si As SlicerItem

Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set sc = wb.SlicerCaches("Slicer_Store")
Set si = Nothing

On Error Resume Next
For Each si In sc.BoxSlicerItems
    If si.Selected = True Then
        Rows("41:2500").EntireRow.Hidden = True
    Else
        Rows("41:2500").EntireRow.Hidden = False
    End If
Next si
End Sub

What i am doing wrong? it's not choosing any stores, please help..

 


Comment by: Jan Karel Pieterse (6/24/2016 11:20:52 AM)
deeplink to this comment

Hi Anna,

Not sure what you are trying to achieve. It seems there is no such property as "BoxSlicerItems", so your code wont work indeed.
Apart from that, why hide rows, the slicer should be taking care of hiding stuff without any VBA code needed?

 


Comment by: Jerry (6/30/2016 4:13:21 AM)
deeplink to this comment

Jan,

I have an xlsm file that previously contained several pivot tables and slicers. Our MAC users wanted to use the workbook so I removed slicers and pivotcharts associated with them to allow the MAC Excel 2011 users to use the application. I verified the slicers were removed with your slicer cache code below and it does not find any slicers in the workbook to report. However, the MAC users receive an warning/error message when opening the file: “This workbook includes content that is not supported by Excel for Mac 2011. *Slicer.

Seems that even after deleting the Slicers some remnant is still in the workbook that Excel Mac 2011 is detecting. Any suggestion on determining what is causing this and how to eliminate it without rewriting the application from scratch.

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
            Debug.Print oSlicercache.Name & ", " & oPT.Parent.Name
        Next
    Next
End Sub


Thank You,
Jerry

 


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

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)
deeplink to this comment

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)
deeplink to this comment

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)
deeplink to this comment

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)
deeplink to this comment

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)
deeplink to this comment

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)
deeplink to this comment

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)
deeplink to this comment

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].