Pivottables are one of the most powerful data analysis features Excel
has to offer. From version to version, Microsoft have added functionality
to this feature. Pivottable Slicers were introduced with Excel 2010 and
as of Excel 2013 you can also insert a slicer to filter a table. In this
article I'll discuss how you can put slicers to work.
If you've ever done filtering on (Pivot) tables before you'll know why
this can be a challenge. But I'll spell it out here in case you haven't.
With any version of Excel, you can filter the content of your table or
Pivottable report (or pivot chart for that matter) by clicking the appropriate
dropdown and checking/unchecking boxes:
Old-style (Pivot) table filtering
This method has a number of drawbacks listed below, in no particular
order:
Users who are not familiar with (Pivot) tables are easily confused
by the filter dropdowns.
To check/uncheck an item you must click in the checkbox, clicking
the item only selects the item, it does not change the check.
You cannot drag across the items to select a number of them.
You cannot modify the appearance of the filter, apart from changing
the size of the entire box.
There is no way of telling which filters are in effect, only that
there is some filtering applied.
If you have more than one Pivottable tied to the same data, then
applying a filter to Pivottable 1 does not necessarily mean Pivottable
2 gets the same filter settings; it takes VBA to keep them in synch
automatically.
If your report has hierarchical data (countries and cities for example),
then a filter on a higher level field in the hierarchy will not change
the items visible in the lower fields. So Excel will happily show Toronto,
even when you've unchecked Canada in the country filter. This makes
filtering on large Pivottables very cumbersome.
Slicers put an end to a number of these problems:
Slicer filtering
They are very intuitive to use.
You can click an item, drag across a number of them, use control
or shift click, in other words they are easier to use.
The appearance of a slicer is very configurable.
You can tie one slicer to as many Pivottables as you like, keeping
them in sync (you cannot do this with table slicers).
You can have multiple "copies" of a slicer operating together on
different worksheets.
Slicers reveal hierarchical data in a sensible manner: non-applicable
values are shaded differently so you'll know they do not apply in the
current filter mode.
Configuring Slicers
Slicer options on the ribbon
There is a host of settings you can apply to your slicers. The Ribbon
shows most of them:
Ribbon (cut in two halves so it fits on this page) showing the slicer options
tab.
The Slicer Tools contextual ribbon tab houses a number of groups to change
your slicer's appearance and behavior. Let me discuss them here.
Slicer group
This group houses three important controls. You can change the name of
a slicer here (Slicer Caption), change some settings controlling which items
are listed in a slicer and how they are sorted and filtered (Slicer Settings).
Also, you can set up which Pivottables connect to
your slicer (I explain that below).
Slicer Styles Group
A quick way to format your slicers is by applying one of the built-in
slicer styles. Like any style in Excel 2010, these adhere to the theme you've
selected. This makes it easy to keep your workbook design tidy and consistent.
Like cell styles you can create your own slicer styles too, by right-clicking
one of them and choosing "Duplicate", followed by right-clicking the newly
duplicated style and choosing "Modify".
Buttons group
Just like you can easily change the colors of your slicers by applying
a different style to the slicer it is simple to modify the button layout
and appearance, just adjust the numbers in that group of the ribbon.
These three controls enable you to change the button layout and size
Size group
And last but not least a small group which lets you edit the button sizes.
Commands not in ribbon
Unfortunately, not everything you need is on the ribbon. The missing
options are quickly accessible through the slicer's right-click menu however.
Select the "Size and Properties" option to get there. Here you can control
whether or not the slicer must be printed and should move/size with cells
just like any other shape on a sheet. And you can turn off resizing and
moving to prevent your users from messing with your layout.
Changing a Slicer's font size
If you've scurried through all slicer dialogs like I have, you'll have
noticed there is no way to change the font size of a slicer.
Or is there? Of course there is, but is has been cunningly hidden!
The basic idea is that you must edit the slicer's style. But since you
can only edit custom styles, the first step is to add a custom style. Pick
one you like best and right-click it, then select Duplicate...:
Right-click a slicer style and select "Duplicate"
The second step is editing the new custom style. Right-click that style
and select "Modify...":
Right-click a slicer style and select "Modify".
In the dialog that pops up, select "Whole Slicer" end then click the
Format button:
The Modify slicer Quick style dialog.
As you can see in the screenshot below, now you can edit the slicer's
Font.
Time to select a nicer font for your slicer!.
After you OK this dialog, probably nothing appears to happen
to your slicer. Don't worry, all that is needed now is applying your new
custom style to the slicer and you're done!
Controlling which pivots are handled by your slicers.
A very useful option of slicers is that you can tie them to more than
one Pivottable and/or -chart.
The way to handle this is by selecting the slicer in question and then
clicking the Pivottable Connections button in the Slicer Settings group
on the Slicer Tools contextual ribbon tab:
the Pivottable Connections button in the Slicer Settings group on the Slicer
Tools contextual ribbon tab.
This brings up the following tiny dialog:
the Pivottable Connections dialog.
As you can see, Excel has listed a couple of Pivottables in your file
to tie the slicer to.
Luckily, Excel is smart enough to ensure that only Pivottables using
the same cache as the selected slicer is tied to are in the list.
If you have more than one slicer on a particular
field (e.g. a slicer on "City" on each sheet with a Pivottable), then you
must take care when selecting which tables the slicer relates to. As soon
as you check more than one check box, the slicers working on the checked
Pivottables will be permanently tied together. The only
way to make the slicers work independently again is by using the Undo button.
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 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
user interface side of things it is the slicer the user actually sees, which
is why I put that in-between.
Also note that this is a diagram for pivot table slicers. A table slicer
has a much simpler diagram where a table has one slicer cache with attached
to that all slicers pertaining to that table. No other tables are involved.
The code below enumerates all slicer caches and their associated pivot
tables in your workbook:
Sub MultiplePivotSlicerCaches() Dim oSlicer
As Slicer Dim oSlicercache
As SlicerCache Dim oPT As
PivotTable Dim oSh As
Worksheet ForEach
oSlicercache In ThisWorkbook.SlicerCaches ForEach oPT In oSlicercache.PivotTables
oPT.Parent.Activate
MsgBox oSlicercache.Name &
"," & oPT.Parent.Name Next Next EndSub
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 slicer cache is deleted (the one belonging
to the Pivottable you checked to add to the current slicer). So the hierarchy
changes to:
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 EndWith EndSub
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" EndWith 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 EndWith EndSub
Synchronising Slicers
Oftentimes a reporting workbook contains more than one pivot table. As
I showed you already, synchronising pivot tables that use the same pivot
cache is very easy if you tie them together with one or more slicers pointing
to both pivot tables. You use the "Pivottable Connections" button on the
ribbon for that.
However, it frequently happens that pivot tables do not share a pivot
cache, but still need to be in sync, for instance if your slicer is filtering
on a reporting period such as Year, Quarter or Month. In the code below
I assume all pivot caches share the fieldnames to filter on. I also assume
the tied-together slicer caches share the same values, in other words, all
years appearing in Slicer_Year also appear in Slicer_Year1 and vice versa.
If your situation differs (values in slicer cache 1 might be missing in
slicer cache 2 or the other way around), you will need to take special precautions.
Setting things up
The first action needed is to make sure each slicer is connected to all
relevant pivot tables belonging to its pivot cache. Take care NOT to add
a pivot table to a slicer for which the field in question is not used in
that pivot table, because a slicer can operate on a field that isn't included
in the pivot table itself. It will filter your pivot table and the pivot
itself gives no clue that it was filtered (other than the slicer).
Suppose you have three reports: Monthly, Quartely and Yearly. You might
have three slicers for those pivot tables: Year, Month and Quarter. But
for the quarterly and yearly pivot table, it makes no sense to tie them
to the Month slicer. In fact, it would turn them all into a monthly report.
In a situation like this, only the Year slicer is a sensible one to connect
to all three pivot tables. If you would want to connect the Quarter slicer
to the Month slicer, you would need some VBA which, after selecting a month,
sets the associated Quarter in the Quarter slicer. The code below isn't
that smart, it just syncs Year, Quarter and Month slicers, assuming you
did all of the slicers pivot table connections correctly.
Triggering a change in a
slicer filter
There is no event tied to slicer actions directly. However, clicking
a slicer triggers the Workbook_SheetPivotTableUpdate event in the ThisWorkbook
module (there is a similar event in the sheet modules, but I want one generic
event which responds to any pivot table update). The empty event stub looks
like this:
Private Sub Workbook_SheetPivotTableUpdate(ByVal
Sh AsObject,
ByVal Target As PivotTable)
End Sub
Sh is an object variable pointing to the worksheet on which a pivot table
just updated. Target is an object variable pointing to the pivot table that
just updated.
The first thing the code needs to do is find out which slicers are connected
to the pivot table that just changed and set a pointer to the ones we want
to use to sync the other pivot tables to.
First, we declare some variables:
Dim oScMonth
As SlicerCache Dim oScKwrt
As SlicerCache Dim oScYear
As SlicerCache Dim oSc As
SlicerCache Dim oPT As
PivotTable Dim oSi As
SlicerItem
The first three are going to hold the Year, Quarter and Month slicers
tied to the changed pivot table. Note that no more than two of these three
are in fact tied to the pivot table: either Year and Month, Year and Quarter
or just Year.
Finding the slicer that was
clicked
So now step through all SlicerCaches and then through all their associated
pivot tables to find out which slicers are tied to the changed pivot table:
ForEach
oSc In ThisWorkbook.SlicerCaches ForEach oPT In oSc.PivotTables If
oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name
Then If
oSc.Name Like "*Year*"
Then Set
oScYear = oSc ElseIf
oSc.Name Like "*Month*"
Then Set
oScMonth = oSc ElseIf
oSc.Name Like "*Quarter*"
Then Set
oScKwrt = oSc EndIf ExitFor EndIf Next IfNot oScYear IsNothing And Not oScMonth
IsNothing And Not
oScKwrt IsNothingThenExitFor Next
As you can see, we only pick up the slicers Month, Quarter and Year because
those are the ones we want to sync.
Synchronising the slicers
Now that we have the slicer(s) that have been clicked, we want to sync
them with slicers with similar names. By Default, Excel names SlicerCaches
like this: Slicer_FieldName. If another slicer with the same fieldname is
added (which adds a SlicerCache too), it is called Slicer_FieldName1, the
next one is called Slicer_FieldName2 and etcetera. So what the code below
does is loop through all slicer caches again, looking at their names to
figure out which ones belong together.
The code has a special trick to avoid problems. Suppose you have a slicer
which has its first item selected and the remaining items de-selected. Suppose
that the sister slicer is clicked and we click the second item. When the
code runs through all slicer items, it will start with the first one, which
will be de-selected. This causes the slicer to select all items, because
it is not allowed to de-select all items of a slicer. Subsequently the code
runs through the remaining items and sets them as needed. The end result
is that both item 1 and item 2 are selected, rather than just item 2. The
trick around this is setting the last slicer item to "Selected" before running
through the loop:
IfNot
oScYear IsNothingThen ForEach oSc In ThisWorkbook.SlicerCaches If
Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <>
oScYear.Name Then 'This
one has a similar fieldname (first three characters are compared in
this case) 'but
not the same name, as that would be the same slicer cache. 'So
synch it with the changed year slicer 'If
a slicer has the very first item selected and you subsequently de-select
it, 'the
end result is that all sliceritems get selected. So select the last
item of the slicer first
oSc.SlicerItems(oSc.SlicerItems.Count).Selected
= True ForEach oSi In oScYear.SlicerItems OnErrorResumeNext If
oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected
Then
oSc.SlicerItems(oSi.Value).Selected
= oSi.Selected EndIf Next EndIf Next EndIf
Putting it all together
So if you put all of this together, this is what you end up with:
'Variable to prevent event looping: Dim mbNoEvent AsBoolean
PrivateSub Workbook_SheetPivotTableUpdate(ByVal
Sh AsObject,
ByVal Target As
PivotTable) Dim oScMonth
As SlicerCache Dim oScKwrt
As SlicerCache Dim oScYear
As SlicerCache Dim oSc As
SlicerCache Dim oPT As
PivotTable Dim oSi As
SlicerItem Dim sYear AsString Dim bUpdate
AsBoolean 'Prevent event looping, changing
a slicer in this routine also triggers this routine If mbNoEvent
ThenExitSub
mbNoEvent = True
bUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False ForEach
oSc In ThisWorkbook.SlicerCaches ForEach oPT In oSc.PivotTables If
oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name
Then If
oSc.Name Like "*Year*"
Then Set
oScYear = oSc ElseIf
oSc.Name Like "*Month*"
Then Set
oScMonth = oSc ElseIf
oSc.Name Like "*Quarter*"
Then Set
oScKwrt = oSc EndIf ExitFor EndIf Next IfNot oScYear IsNothing And Not oScMonth
IsNothing And
Not oScKwrt IsNothingThenExitFor Next IfNot
oScYear IsNothingThen ForEach oSc In ThisWorkbook.SlicerCaches If
Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <>
oScYear.Name Then 'This
one has a similar fieldname (first three characters are compared in
this case) 'but
not the same name, as that would be the same slicer cache. 'So
synch it with the changed year slicer 'If
a slicer has the very first item selected and you subsequently de-select
it, 'the
end result is that all sliceritems get selected. So select the last
item of the slicer first
oSc.SlicerItems(oSc.SlicerItems.Count).Selected
= True ForEach oSi In oScYear.SlicerItems OnErrorResumeNext If
oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected
Then
oSc.SlicerItems(oSi.Value).Selected
= oSi.Selected EndIf Next EndIf Next EndIf IfNot
oScKwrt IsNothingThen ForEach oSc In ThisWorkbook.SlicerCaches If
Mid(oSc.Name, 7, 3) = Mid(oScKwrt.Name, 7, 3) And oSc.Name <>
oScKwrt.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected
= True ForEach oSi In oScKwrt.SlicerItems OnErrorResumeNext If
oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected
Then
oSc.SlicerItems(oSi.Value).Selected
= oSi.Selected EndIf Next EndIf Next EndIf If Not oScMonth
IsNothingThen ForEach oSc In ThisWorkbook.SlicerCaches If
Mid(oSc.Name, 7, 3) = Mid(oScMonth.Name, 7, 3) And oSc.Name <>
oScMonth.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected
= True ForEach oSi In oScMonth.SlicerItems OnErrorResumeNext If
oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected
Then
oSc.SlicerItems(oSi.Value).Selected
= oSi.Selected EndIf Next EndIf Next EndIf
mbNoEvent = False
Application.ScreenUpdating = bUpdate EndSub
Show Selected Items
Even though it is often sufficient to see the slicer on the workbook
it can be useful to be able to get a list of the filtered sliceritems in
a worksheet cell. If your pivot table is built using a Power Pivot model,
then you can access this information by using the CUBERANKEDMEMBER worksheet
function. If however the slicer was built off of a "normal" pivot table,
then you need a bit of VBA code in a User Defined Function (UDF).
The slicer name
In order to retrieve the selected slicer items we need slicer's internal
name, both for the CUBERANKEDMEMBER function and for the UDF. This name
can be shown in the user interface by right-clicking the slicer in question
and selecting "Slicer settings". The name you are looking for is marked
in red in the screenshot below and indicated by "Name to use in formulas":
CUBERANKEDMEMBER (for
Power Pivot slicers)
The CUBERANKEDMEMBER function returns an array of selected items and
hence needs to be into as many (vertical) cells as you expect will be selected
in the slicer. See the screenshot below.
Note, that this is a dynamic array formula that only works in Excel 365.
For older versions you should use a formula like the one below and copy
that formula down far enough:
The code sample below must be placed in a normal module:
PublicFunction
GetSelectedSlicerItems(SlicerName AsString) AsString Dim oSc As
SlicerCache Dim oSi As
SlicerItem Dim lCt AsLong OnErrorResumeNext
Application.Volatile Set oSc = ThisWorkbook.SlicerCaches(SlicerName) IfNot
oSc IsNothingThen ForEach oSi In oSc.SlicerItems If
oSi.Selected Then
GetSelectedSlicerItems
= GetSelectedSlicerItems & oSi.Name & ", "
lCt
= lCt + 1 EndIf Next If Len(GetSelectedSlicerItems) >
0 Then If
lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems
= "All Items" Else
GetSelectedSlicerItems
= Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) EndIf Else
GetSelectedSlicerItems
= "No items selected" EndIf Else
GetSelectedSlicerItems = "No slicer
with name '" & SlicerName & "' was found" EndIf EndFunction
The function takes the slicer name as its argument and returns a text
string (comma delimited) containing the selected items. In a worksheet cell
the formula would look like this:
=GetSelectedSlicerItems("Slicer_TeamID2")
Conclusion
Well, I hope I got you started with getting your head around how slicers
work and how to address them using VBA. I find Slicers a real gem in Excel.
A great addition to the product!
Dear, I recently purchased a new Dell optiplex 380 with a MS excel non commercial use,, but i dont see this Slicers ribbon/options, tools in my excel while playing with pivots....How should i debug this ?
What Office version are you using? SLicers were introduced with Excel 2010 (=Office 2010) and are unavailable in Excel 2007 and older.
If you do have 2010, make sure you select a cell inside a pivot table so that the Pivot tools tab is visible on the ribbon. The slicer button is on that tab.
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.
Hi,
I am using excel 2010 but can not find slicer option on ribbon. Evenif i select a cell in piot table it doesnot show slicer option in pivot table tab.
It is located in the Sort & FIlter group, on the Options tab of the Pivottable Tools contextual tab of the ribbon. Note that this option is disabled if you are in a pivot table in a file that was saved in Excel 97/2003 format. It has to be a "normal" Excel 2007/2010 format workbook.
Slicers are the best part of Excel 2010 - especially the ease with which one can connect multiple pivot tables with a single slicer.
Unfortunately there appears to be not backward compatibility with Excel 2007 at all (I'm surprised Microsoft have not come up with a "compatibility pack") - even in xlsm format.
Is there a way of maintaining the pivot connections when opening a "slicered" 2010 workbook in 2007? (though in 2007 I know the old style "check box" filters would have to be used)
Not sure what is supposed to happen. The information I had so far was that 2010 objects are ignored by Excel 2007, but still included in a save. So I was expecting them to carry over unscathed. Are you saying they don't?
If so, it may be useful to send me the workbook (the version before 2007 and after 2007 has touched it) so I can issue a bug report.
I think the question may be slightly misrepresenting the issue. If you have already created a slicer, the Slicers ribbon/options, tools is a contextual tab/ribbon option that shows when you select the slicer itself. It will show up as the right most ribbon option when the slicer is selected.
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
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?
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
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.
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
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.
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)
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.
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
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.
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
I'm afraid you cannot tie one (set of) slicer(s) to more than one Pivot cache. However, you can use a bit of VBA code to mimick that behaviour. Find some examples in the comments of the next page:
Can i put slicer on the same page as the Pivot table?. I am trying to make it look like how we select from the drop down and get the related information in pivot table..
When i should connect my slicer to more then one of my pivot tables, there comes a message that it "the slicer can not currenty be created or connected to the pivot table". What's wrong ?
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
Hi
When I try to add more than one pivot table connections to my slicer, I got a message that "An error occured and the slicer can not currently be created or connected to pivot tables". What's wrong ?
You can only add pivot tables form the same pivot table cache to one slicer, perhaps the pivot tables you are trying to combine are independently tied to their own source?
Thank you for the answer.
All pivot tables i try to connect to my slicer are using an area that I've named "rawdata" as datasource. This area is just in another sheet in the same workbook.
When checking name manager, I can see that "rawdata" is right defined, however, my slicers are also listed in the name manager, but they have no reference...
/Kajsa-Stina
Slicers do not have a reference in Name Manager, that is "by design". How many Pivot caches does your workbook have?
Alt+F11 to the VBA editor
control+g to the immediate window
type:
Once you've created a custom slicer template, is it possible to save it and access it on another workbook? I've created my own slicer template (color, font, shading, etc) that I want to use in different workbooks, but the "created" templates are only available within that one workbook.
Cheers,
Aaron
A quick way is to open the workbook with the custom slicer style applied to a slicer (not sure of that is necesary) and copy the slicer and paste it into the other workbook. The slicer style is copied with it.
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
RE: Excel 2010. I have a slicer with 6 choices. I want to show only 4 of the 6 (2 of the 6. And, of the 4 that I want visible, I want to change the text. How do I do this?
I think you can only achieve that by removing data from the source so that those items are not on the slicer. Also, you cannot change what is shown on the slicer, other than by changing the source data.
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
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?
How do I change the date format in a slicer button? My table data has the date as 6/1/2011, but I've formatted it to display Jun-11. However, the slicer button still displays 6/1/2011.
I have slicers on one tab and pivot tables on another.
When I copy these tabs to a new workbook, my slicers do not copy (or macro buttons for that matter). They used to copy fine when I went through this process, but I must have changed something along the way.
I have tried the copy process using all the tabs and just single tabs. I have also tried it using the tab drag process from one open workbook to another and still lose my slicers and buttons.
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?
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
I am using slicers in a financial application I created and it works great filtering the data, but when you drill down to see the details, it does not filter the data at all. Can you give me some insight on how to prevent this from happening. I don't understand why it does not filter the detail data.
This is one of those situations where a sample file is needed to see what is going on. Please use the email address below to send me one (if possible).
I have created my slicer and formatted it to match my table but I need to display only months and not the full date so that I can sort my table by month. How do I modify my date so that it only shows the month and not the full date? Thanks
Does anyone know how I can get a slicer search bar? I want to be able to type something into the search bar and press enter and have it select what I typed in the slicer.
When I tried to add more than one pivot table connections to my slicer, I get a message stating "An error occured and the slicer can not currently be created or connected to pivot tables". Why I am I unable to connect? I've just 1 Pivot Cache (checked as per your instructions above.
I am facing the same problem as Kajsa-Stina (2/5/2012 11:46:01 PM). I was curious to know if you have found a work around to it instead of re-creating all the tables.
Perhaps Excel thinks that the pivot table was created in an older version of Excel (I have seen that happen to files which were created in Excel 2007). Try recreating the pivot table. Also: make sure the file is NOT saved in Excel 97-2003 file format.
My fiscal year runs from July to June. After creating my pivot table and inserting a slicer for the months, the slicer automatically sorts it from January to December. I cannot find an option to customize the sort order of the slicer.
I am trying to use slice, but when I go to set it up the slice icon is dimmed in the tool bar. Am I missing something? How do i get the slice icon to enable?
David
The pivot table you are trying to connect to a slicer must be in a file create by Excel 2010 and saved in the 2010 file format (xlsb, xlsx or xlsm). Pivot tables created in earlier versions or in files of older Excel versions cannot be combined with a slicer.
I have Excel 2010 with Win 7 -- I can neither modify nor delete custom styles (either Pivot Table or Slicer) and it's driving me CRAZY! The options are available for me to click, but nothing happens after I do. Seen it? Got a fix?
Okay, when I first saw slicers, I thought, yeah, they're nice in that they look better than pivot table report filters, but that's really all they are - nicer looking pivot table report filters.
Then I read this article and discovered that they will apply your hierarchical groupings to your filtering. Okay. That's cool. I think more of slicers now than I did two minutes ago (I'm a slow typer ;-))
I have a pivot table in which I use two fields as report filters. The first field is a set of item groups, and the second field is a set of items contained in an item group. The various item groups have differrent sets of items. I would like to set up two slicers (item group, and item) such that when I pick a value in the item group slicer, the item slicer only shows the values appropriate for the selected item group. Can this be done, and if so how. I appreciate your help. Thanks.
Slicers offer that option by default, but how they behave when items are not applicable is something you can control in the slicer options dialog, which is accessible through a right-click on the slicer and select "Slicer settings".
However, you cannot set it to not show non-applicable items, you can only make sure those are shown at the bottom of the list and in a different color.
I have designed a pivottable, and I want to use the slicer tools. My idea is to use a togglebutton, so if it is activated it will show the slicer, and deactivated it will not appear. Is that possible?
Sure that is possible, but it requires a bit of VBA which you tie to the toggle button (though I would probably a checkbox instead).
I added a checkbox from the forms toolbar to a worksheet. The checkbox's name is "Check Box 1". I assigned this macro to the checkbox:
Sub CheckBox1_Click()
Dim oSl As Slicer
Dim oSc As SlicerCache
For Each oSc In ThisWorkbook.SlicerCaches
For Each oSl In oSc.Slicers
If oSl.Parent.Name = ActiveSheet.Name Then
If ActiveSheet.CheckBoxes("Check box 1").Value = -4146 Then
oSl.Height = 0
Else
oSl.Height = 100
End If
End If
Next
Next
End Sub
Note that you cannot hide a slicer, only set its heigh or width to zero. This means if you want to show the slicer again, you must remember what its original height was. I did not include any logic in this macro to handle that though.
I am trying to copy a slicer from one workbook to another. The slicer i pasted is no longer linked to the pivotable on the original file i copied from. How can i do it?
One of my slicers contains a significantly long list of names. I realize that if I need to do a search I can do so in the pivot table itself. However, is there a way I can give the user the ability to do this in the slicer?
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.
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.
Can you get a total for the slicer's selection? For example, If I have the slicer set for January, can I get a total for all the amounts the slicer has selected for January?
If the slicer is filtering the page filter, perhaps the column where the page filter is located is too narrow for certain values If that cell has word wrap on, it will force Excel to increase row height to display the full value.
I have been using slicers as a great way to indicate what areas of our business that needs attention. I want to do this once a month. But when I try to update the pivot table/ refresh, nothing changes in the slicers. Help would be much appropriated :) Anne
There should be an option in the slicer settings that allows you to indicate whether or not to have the slicer include items which are no longer in the pivotcache. Is that what you are after?
This is the message I get when I try to update my with the slicers; "The data source of a PivotTable connected to the slicers that are also connected to the Pivot Tabel cannot be changed. To change the data source first disconnet the slizers from this and other pivottables".
So my question to you is, is there any way to get around this? I would really like to keep my slizers and not have to do them wach time i update my pivottable.
Odd, I just created a PT bound to an Access database, with one slicer connected to it. If I change the PT source (by editing the connection properties), it just lets me do that without complaining.
Like Ricardo I'm having problems with my slicers resizing my pivot table, though mine are changing the column width:
-My table is set to not resize on update.
-The values in my table are never more than 2 digits.
-The row labels are abbreviated month names.
-It doesn't matter where on the sheet the slicer is.
-It doesn't help if I stretch the columns out to allow some extra room.
-Whenever I change the months the row label column resizes.
I think it may be seeing the full name of the month even though it shows the abbreviated name, but if I stretch it to allow room for this it'll shrink for shorter month names.
Awesome job... I read through every post. Its really a task to keep replying.. But We all truly appreciate.
I read through all the post but may have missed this.
I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this.
Thanks again.
I'm afraid I can't send it to you as it contains sensitive company data. I tried to remove all the personal data and give you a version with just the sheets in question, and what do you know; the problem went away!
I had a feeling the gentleman who created this workbook might be behind the fault so I looked through the page and found, right at the bottom, another pivot table, with autofit on!
Well, at least its solved, and I've got another horror story to share with the wife :)
Did you find a solution to the problem posted by T Pierce on 5/22/12? I am having the same problem. I want to have slicers in a worksheet that are linked to multiple pivot tables and charts, and be able to drill into the details for just the results filtered by the slicers. Thanks!
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
one big problem I can't get past with slicers is that that deleting or modifying a custom slicer does not work. The only way I have of modifying a customer slicer is to duplicate it, the modify box then pops up. I now have 11 custom slicers in various stages and cannot delete any. Is there some trick to get modify and delete to work.
The prompt dialogue boxes come up but don't actually do anything.
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.
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.
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
I expect you are referring to a custom slicer style?
Some important things to know:
1. You can only modify a custom slicer style. Built-in styles cannot be modified.
2. (and I think this is the one you need!) After duplicating a built-in style and modifying it, you have to apply the new custom slicer style to the slicer in question. Only then do your modifications appear.
thanks Jan, yes it is a custom style. I can modify after duplicating but then cannot modify it again unless I duplicate again. Also cannot delete it. I want to modify a style check it then make more modifications until I have what I want.
I can modify them as much as I like after creating a custom one. I can also delete them, even though they are in use on a slicer.
Perhaps your worksheet/workbook is protected?
Hi
I saved my Excel 2010 workbook with 10 pivot tables connected to 1 slicer. Works perfectly. Now I have added additional pivot tables but cannot get them to list in the slicer connections. Is there a limit to the number of connections or is there a trick I am missing. Thank you in advance.
Hi
I am using pivot tables and slicers to create dashboard reports. The info you have provided on this web page is fantastic and very well put together.
I have a large pivot table with fields such as cost centres, cost, date and product code. I have created slicers for cost centres and product code.
The individual cost centres are grouped into North Division and South Division.
My question is can I create slicers for individual cost centres, all North Division cost centres, all South Division cost centres and finally whole Group cost centres? I hope this makes sense! Would very much appreciate your thoughts or advice.
No, all you can do is put a slicer up there that filters on Division. The cost center slicer will then (if properly setup) show the relevant cost centers at the top and the other ones greyed out near the bottom.
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.
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.
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.
Can I connect two slicers (having the same dataset)?
My requirement is to change the data in slicer2 depending on the input from Slicer1. Accordingly the data in the pivot tables change.
I'm not sure what you mean by "connecting" them, the slicers are connected and do reflect filters of other slicers (of you have set the properties accordingly).
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
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
If you copy a slicer with that slicer style applied to a fresh workbook and then save-as that workbook as book.xltx (or m) to your xlstart folder the slicerstyle should become available to each new workbook you start with control+n.
I am sending out a pivot table to several folks and I'm using my first slicer. Lovely tool. However, I typically protect the sheet to protect those folks from themselves. :)
You know, accidentally changing up the columns, etc. When I do that, the slicer stops allowing the user to select a different item, even tho the users of the pivot table can still change the sort or perform additional filtering within the pivot table itself. Is the slicer not considered part of the pivot table when I select my protection options?
Go to the “size and properties” dialog and uncheck the “locked” property for the slicer, THEN protect the sheet with “use PivotTables” (if you’re using pivot slicer) or “use autofilter” (if you’re using table slicer in Excel 2013).
I use slicers a lot. I used to be able to copy them around my excel file.
Today I was able to create a slicer at the pivottable sheet, but I could not copy paste it to my pivotgraph sheet. It was pasted as an image.
I also do not have the opportunity to add a slicer when I have the graph selected. Something which normally is possible.
Is there something I can do to fix this?
I need the slicers a lot for creating a dashboard but I have to be able to move the slicers around my excel file.
I haven't heard of that problem before. Is the chart a chart embedded on a sheet or on a chartsheet? Perhaps chartsheets cannot hold slicers?
Perhaps the file/sheet is protected?
I am not 100% sure, but I believe that chart sheets cannot hold slicers.
I placed the graph on a regular sheet and everything worked fine.
Although it would be nice to use slicers with multiple data sheets, I know this is not going to happen any soon. So for this moment I will have to create multiple slicers.
I'd like to connect 1 slicer to pivottables from different data sources. something which is not possible...
for example: I have 2 data sources. data sheet 1 and data sheet 2. In both the field 'account' is available and in both the values are the same.
I want to have 1 slicer to with which I can filter the account value in all the connected pivottables.
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.
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
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)
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.
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.
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.
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.
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..
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.
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
Hi,
I saw someone asked the same question but i can't find the answer. I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this?? Thanks
I have pivot table with Name sort in order of "Z, D, H, I, F" ; a custom order. When I do slicer, I ask for "use custom list when sorting". but there is no way to disable the radial dial for "ascending" / "decending"; so the items all listed different from the above order. How can I have slicer item listed is a specific order?
The easiest would be something like this:
- Change the slicer filter
- Start recording a macro
- Correct the chart so it displays how you like it
- Stop recording
- Put a button on the sheet that holds the slicer, which you attach to the recorded macro.
- Instruct user to push button after changning filter.
It is possible to run a macro after a pivot table has been refreshed automatically, but it is a bit harder to explain :-)
Odd. I just tried with a short table containing only the letters you mentioned, each in a separate cell in the order you mention. I added that table to my custom sort lists. Then I created a pivot table based on the same list and added that field as a slicer. The order was identical to the order of the list and changing the Ascending to descending just reversed the order, as expected.
I want to insert a slicer from a database of over 1000 items but only want to include 11 items from that list in my slicer, is that possible if so how?
The only way I can see you do that is by filtering the database on the field that determines which items to show. Even then, the slicer will show the other values, but the ones that do not apply are colored in a lighter fill.
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
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.
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
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.
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......
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?
I have a pivot table/chart that I have connected to 4 slicers. The slicers are for different levels of data on the chart: District, manager, lead-office, and office. When a district (manager, etc) is selected in a slicer, I want the other slicers to grey out members that do not belong in the selected grouping. I have one column label field in pivot that includes some calculated items. All of this works until one of the calculated items is selected in pivot, then all the slicers lose their ability to grey out non-selected groupings. When calulated items are deselected, this ability returns.
Column label field selections are done through combo boxes and VB code. This is limit choices to desired items.
Slicer fileds are all in the report filter section of pivot.
Data is MS query cannection to an Access database.
Calulated items are ratios.
There is one row label field.
Data query is very large(over 1/2 million rows).
I need slicers to keep filter ability even with calculaed items. Is there a property I can set to keep filters working on calculated items? Or another way to get the desired filtering to work? Any ideas?
I'm using exel 2010. File saved in xx.xlsx . No slicer. No options is displaced in sort & filter option. Tried a solutions to fix this in wed..... Nothing works....pls help....
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
i read above lots of people facing difficulties in deleting a slicer setting...i just found a solution!!
..i had lots of protected sheets in my file...i had to unhide all the sheets in the workbook (even the very unhidden ones) and UNprotect them all ...i had to also UNprotect my workbook
BINGO!! ...post this i was able to modify / delete my slicer settings!!
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?
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.
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.
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.
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.
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?
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. :)
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?
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
I would like to set manually order of the list items inside slicer...at the moment I can only see the option of alphabetical order under "slicer settings"....is there any possibility to set them manually in the order I want? thanks for yoru answer, Iza
I have a list of records. Each record has a date. I want a slicer that selects records with dates which fall in a range between two dates. How can I do that.
Kathy, create a slicer against the date field. On the slicer, select the date for the beginning of the range. While holding the <shift> key, select the date for the end of your desired range. It will select records w/dates between the two dates. (holding <cntl> will allow you to select/de-select noncontiguous selections)
We actually have excel files tied to a SQL database and are creating pivot tables off the database. I want to put a slicer on those pivot tables. So we want the slicer to look ahead -- that is I want to set it up to find records that haven't been created yet. We have a weekly report and on Monday I want the slicer set to capture all events entered thru the end of the week. That is why we want to create a date range. But the slicers will only let me select dates for which records exist. Any ideas?
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.
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
Is it possible for a pivot chart to have a text box or header which lists the option chosen in a slicer? For example, slicer lists different products sold A, B C,etc., chart lists # of products sold, can chart include the name of the products choosen in the slier box in its heading?
Not easily. If the PT is built off of a powerpivot model then you can use the CUBERANKEDMEMBER function like so (array-entered into as many cells as needed):
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
Hi, is it possible to get the results of slicers selection either as a msgbox or as a text box? If yes, can you pls help with it.
For example: referring to your example of slicer - if the Shipcountry is selected as "Austria" it should display the result at the bottom of the table/chart.
See this page:
https://jkp-ads.com/Articles/slicers03.asp There is a comment from me that shows a function called "GetSelectedItemsOfSlicer". You can use that function to get the selected items.
I'm not sure what you men by a horizontal slicer. DAre you referring to the timeline slicer? This slicer type was introduced with Office 2013 and has no Office 2010 equivalent I'm afraid.
hi Kelvin,
I think you are trying to have the display horizotally, right?
right click on the slicer, select size & properties, position and layout and increas the number of columns. this will arrange the items in the slicer horizontally into the number of columns
Dear Jan,
I created a pivot table on Excel 2010 and I noticed the field list also generates provisions for Month, Quarter and Year (i think it pulls this from the date column in the source table with format e.g. 2 Jan, 2013). This makes it easy to create a slice with the year and dates.
The problem is, I often get feedback form users that the slicers have 'disappeared' on refresh of the pivot table. I realise it's because the month, quarter and year are now missing from the field list. How do i reverse this please?
Hello Jan,
I realised this and had corrected the date errors. I was hoping a refresh of the pivot table would correct bring back the months and Year. I even created another pivot table from the same report but the field list still did not contain the options for month and year.
Please advise!
YOu will have to re-group the date field to get the year and month back.
Alternatively, just add a calculated year and month column to your pivot table's source table. Make sure the formulas in question cater for missing dates (otherwise the year will show as 1900)
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
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 https://jkp-ads.com/Articles/slicers03.asp?AllComments=True and look for it.
I have numerous pivot table charts with clustered columns which reflect a list of upto 10 numbered variables, each is a different error code. I want the columns reflecting each error code to have a fixed colour but I dont seem able to achieve this. When the pivot tables are amended with additional data for a new month the columns change colour.
Unfortunately, PivotCharts don't always retain formatting modifications when they are refreshed. What is retained depends on the Excel version you are using. Older versions retain less and less.
Your best bet is to record a macro whilst setting up everything so you can run the macro after a refresh.
Great technique, thanks for sharing! It's probably obvious to some, but you might want to mention that you will need to hide the additional slicers or move them out of view.
The Selection Pane can be used to Show/Hide the duplicate slicers, and it is also a good place to see a listing of all the slicer names.
I wrote some code over at the Contextures blog some time back for syncing pivots that set up a temp slicer to sync them if they shared the same cache, and otherwise used a Dictionary to store just the visible items of the ‘master’ field in that dictionary, so that those settings could be applied again and again to the various ‘slave’ pivots on other caches.
Using a Dictionary approach on pivots with different caches was very fast in the case that you had thousands of PivotItems in your PivotFields and multiple pivots to sync, as you only had to iterate through the master field the one time to record just the visible items, and then could clear the slave field and then just hide any items in the slave that were NOT in the dictionary.
That code’s due for a refresh, as the bit that set up slicers (for pivots on the same cache) errors out if the user has already set up a slicer. Plus it strikes me that I can make it even faster if say I’ve got 3 pivots that share cache A and 3 pivots that share cache B, by joining up all the pivots in Cache A with slicers, then joining up all the pivots in cache B with slicers, then syncing just one pivot on cache A with just one pivot on cache B and let the slicers sync the rest.
Will work it up and post over at DDOE in due course.
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
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.
I am working on a project at work where I am looking at the same data for multiple months, each month is its own file and contains identical tabs.
I created a new file to use as my dashboard, with the tabs 1) Dashboard, 2) Jan Pivots 3) Feb Pivots, etc...
I started with the original data sets and created three pivot tables for each months data (Date, Grade, Yes/No). I then copy and pasted them into my new file's corresponding tab (all three jan pivots were pasted in my jan pivots tab.)
I have linked all of the cells on my Dashboard tab through VlookUps to my other tabs. Now I would like to use a slicer (if possible) to update every months data on my Dashboard. The problem I am running into is that each pivot table if I were to try and add a slicer to it only recognizes one connection (to its own pivot table). Is there an EASY uncomplicated way (I have no clue how to do anything with code or VBAs) of using slicer to update my dashboard? Hopefully I have included all the details necessary to identify where I may be going wrong or creating problems for myself with the way I set it up? I don't know any help would be greatly appreciated.
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
You declared oS1 as SlicerCacheLevel, but the for loop runs through the SlicerCaches collection, thus requiring an object variable of type SlicerCache.
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 :-(
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
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.
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.
Really enjoy learning about more indepth functionality that can extracted from Excel. Thanks for sharing. Could you provide UDF code that would put the data in a list format (similar to CubeRankMember function) vs a delimited format?
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
In my new dashboard I creating , I have 5 charts all with individual tab Pivots and 1 tab with all the data.
I build Cell site
What I'm trying to accomplish is to show which Cell Sites are completed (MS108) yet pending Transport (MS149/150). I select ALL of MS108 fields and only select the PENDING on the MS149 which pulls out all site pending Transport. The Remaining Milestones(MS), End to End - Fiber to MPOE; Room Ready, and Site walk and select on those that are pending. Each Milestone has it's own Pie Chart. so when I click on a certain Market; I.e. Ohip/WPA all charts work in synch and change they're data labels.
I have two issues:
1)All the data Labels are the same when I click on a Slicer (could this be because I have all the Pivot connection selected ?
2) Each time I select another slicer, my individual Pivot change and it's difficult to get back to square one where nothing is selected.
Hi I have been trying to replicate this code for a project of my own for a few days on and off. I've found it really helpful, but if you follow the guide step by step it doesn't mention this part which is in the full script:
"'Prevent event looping, changing a slicer in this routine also triggers this routine
If mbNoEvent Then Exit Sub
mbNoEvent = True
bUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False"
I am a relative novice for VBA so it didn't occur to me that this was the reason my code was falling on it's face every time. It might just be worth pointing out!
Thanks for such a wonderful function, it works perfectly fine with ordinary pivots and slicers, however, when i use PowerPivots, it throws application defined or object defined error on "For Each oSi In oSc.SlicerItems", I am not sure, why it works fine with ordinary pivots and when i am using powerpivot in excel 2103, it fails.
I'm using Excel 2010 and I've created a dashboard using a pivot table and slicers. Is it possible to create a slicer that has like drop box characteristics that would enable to have 1 slicer but be able to see all of the data in that one header?
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).
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.
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!!
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
I am trying to implement the above code - however some of the pivot tabels do not have all the field
Pivot table 1. Global, Regional, Sub_Regional, Country
Pivot table 2. has the above + Class_code
Pivot table 3. has Region, Sub_Region and Country
I get random results - in your article it says "If your situation differs (values in slicercache 1 might be missing in slicercache 2 or the other way around), you will need to take special precautions"
Pls advice what is the best way to sync all these slicers.
(1004:Application-defined or object-defined error)
It is acting as thought the object have not been instantiated even though it makes it that far. It makes me think that the version of Excel (VBA) I am using (the latest version) is somehow working differently. that maybe the object model is an older one in this code.
1. Added slicer
2. when I select one Criteria in slicer I get data in pivot according to that criteria.
3. But when we double click on the figure for that criteria
it gives full detailed data from that actual sheet instead of the only showing that data as per the criteria.
Odd, as that normally also does not happen without a slicer. A slicer is just an additional way to operate the pivottable filters, the pivottables functionality stays the same.
I have a pivot with a country field which has values such as
1. AU
2. US
3. AU; IN; TH; JP
and so on.
Is it possible to have a slicer or data validation list where the user can just type 'AU' and the pivot automatically selects all those records which have 'AU' in the country field?
Thanks in advance.
Thanks Jan, for your reply. If that's the case where it can be done only by the "normal" pivotfield filter dropdowns, is there a way to capture what was typed? For example, if the user types "AU" in cell B1 in the filter drop down, it shows as "(Multiple Items)", but can I then have "AU" populated in cell C1, based on what was entered?
I have a workbook where I have a slicer connected to a small table (the table just shows the Last 2 months of a period)- eg: Dec and Jan
The problem I have is that when the month changes and the pivot table updates to Jan and Feb - the slicer still keeps "Dec" even though it is not in the table anymore
Just wanted to say thanks...your code was exactly what I needed to retrieve the slicer item selected by the user and then apply that to a heading filter. I can do this naturally in Excel 2013, but my company is still using Excel 2010.
I'm having a bit of an issue with my slicers and was hoping to find a solution.
I am trying to create a dashboard so that certain responsible persons can verify and confirm a payroll. Our policies are such that pay can be very different from one check to the next for any number of codes. I have pulled in two pivot tables based on a report that looks at all of the different earning codes on the check run that needs to be confirmed, and the previous check run, which has already been paid.
One pivot table summarizes the data by earning code and each check run is a separate column. For example, Regular earning for 2/4 is $350k and for 2/18 it is $345k. Training pay for 2/4 is zero and for 2/18 it is $5k.
The other pivot table summarizes the data by person and again each check run is a separate column. The difference on this, is that I have the data to display the difference from the previous check run. So for Rick, the total pay for 2/4 is blank (because it is the basis for comparison) and for 2/18 it is $1000 because he earned $1000 more on 2/18.
I have a slicer for earning codes and it links to both pivot tables. If I click on the training pay code in the slicer, the pivot tables act in the following fashion. The first pivot table, the one that summarizes codes, hides the 2/4 column and only shows $5k under the 2/18 column. The second pivot table hides the 2/4 column and for the 2/18 column goes blank. It no longer shows the difference from the previous pay date. Is this because it is hidden so the 2/18 now becomes the basis for comparison? If so, is there a way to force columns on pivot tables to remain visible even if there is no data?
It's nice that the second table only shows the employees who have training pay, but since it doesn't show any amounts next to them, it's not as useful as it could be.
I expect the problem is not caused by the slicers, but rather by the setup of the pivottables. If you have fields that are calculated based off of the previous value, filtering affects the results. I would need to see your workbook in order to give a more useful advice though.
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
I have a Slicer that I want to be able to put them in the correct order. The only option I see is Alpha.
My segments are U50, 50-99,100-499,500-999,1000-2999, 3000+. When I put them in a slicer the order is wrong. How do I fix that?
You should be able to add a custom list to Excel containing the needed order and then the slicer should sort using that list.
Select File, Options, Advanced. Scroll down to bottom of screen and click "Edit custom lists". Click in the box next to "Import" and select the range of cells which have the list in the order you need.
Hi,
Does anyone have this working in a file they can share please? I am having issues getting the code to work. I have multiple external data sources and multiple slicers, so this looks like a very good solution.
Hi Samm,
I have a question around the number formatting of the entries that show up in the slicer window. The column (field) that I wish to add to the slicer is a currency column with 2 decimal places. For e.g. $1.00, $1.05, etc. which indicate the exchange rates. When I add this column to a slicer, the entries in the slicer window (where you choose from) show up as 1, 1.05, 1.1, etc. i.e. the formatting reverts back to general. Would you know how to fix this?
> Let's say you have 2 slicers, one CITIES and one SHIP COUNTRY.
>
> If you select "Austria" in SHIP COUNTRY, only a select number of CITIES are now valid as selection in the CITIES slicer.
Then replied that it had been solved.
@Maria/JKP: How did you solve it? I have a similar requirement:
1) display in a comma-delimited list all slicer items that are still valid (not grayed out) after any filter is changed.
2) if all items are still valid it should show the entire list.
3) if no filter is active it should default to "no active filters" or similar insstead of listing every item in that slicer.
Slicer 1 ('Flavor') has value "Apple" selected, which is captured by the "selected items" method on this page. All items in Slicer 2 are still in scope so the results list cell for 'Grocery' category should display "Yogurt, Candy, Soda". Only one item in Slicer 3 is still in scope so that cell should display 'BestCo', like this:
Selection(s) and current scope
---------------------------------------
[Label Column | Data Column ]
[---------------------------------------]
[Flavor Selection: | Apple ]
[Grocery in scope: | Yogurt, Candy, Soda]
[Brands in scope: | BestCo ]
---------------------------------------
Create a workbook with a custom slicerstyle. When done and happy with it, copy a slicer with tht style applied to a new empty workbook.
Delete the sycer, the style should stay in the workbook.
Save-as the workbook as book.xltx to your XLSTART folder.
Hi, there is one problem I'm facing when I use the UDF code. I pasted the code in one of my modules and it works fine.I have a few other Sub routines in my other modules. When I run some of them, on reaching an autofilter statement they are getting automatically redirected to this function but I'm not able to figure why this is happening. Can you please help me regarding the same. Why will a sub get redirected to a function without being called?
Regards,
Pooja
This is because when you cause a recalc during debugging code, Excel's calculation engine will make calls to the User Defined functions and you will be stepping through them each time the function is called.
You can get through the UDF calls in one keystroke during debugging by hitting control+shift+F8.
Does that mean the redirecting happens only when we run the code line by line (F8)? I'm worried that the redirection is slowing down my other subs when I run them at once (using F5) as well.
Oh! So that means it will slow down my subs right? Is there any way I can prevent this?
(Can't use Application.Calculation=xlCalculationManual as my code depends on some of the calculated fields from the worksheet)
You could add a switch to your UDF that turns it off, but that will probably cause their calling cells to get wrong results as well. You could control whether an individual worksheet is allowed to recalc perhaps?
I never use VBA but your code here is exactly what I need.
However, I am so new that I don t know where to put the code (my slicers are copied into a worksheet but the pivot tables are in another one (this code should be where the slicers are true?)
then my other question is how can I put only one selection field for eac slicers that makes your code run?
in my example I have 10 to 15 pivot tables. These pivot tables are based on 3 data sources, meaning that for the same selection field (I have 3 Country, Station, Months) I have 3 slicers for each filter...)
the idea to make it friendly for other user is to have only one slicer for month, one for country and one for station that allows to synchronise the 9 slicers I had to create.
I hope it is not to confused and you can help me.
1- in your code the only part I don t understand is the following Dim sYear As String .
I don t follow why it has been added, is it because the Year (in your code) is the only slicer linking all the pivottables (same filter in each one)?
In this case, if I have 3 slicers filtering in each pivottables (some linked with the same datasource and some with others), shall I add the same Dim for each slicers caption name?
2- IS your code valid for slicers that have the same caption name but each slicer comes a different a different data source
example Months from datasource 1, Month from data source 2 and Month from data source 3?
3- If the answer is yes, if I only click on one slicer, all the slicers having the same name will synchronise (eg. Month one)?
Your code is just amazing and works (please forget my previous questions except the one).
I have a difference in the setting because one of my slicers caché referring to station is having a slicercaché of station in one slicer and origin_city in the other 2.
moreover the list of station/city is different as a result the same station as the first one is selected but another 2 are also selected in the longer list of cities.
How can I send you the code to see how I can fix it?
Thanks for your help :)
1: sYear is not used so the declaration can be removed :-)
2: The code is ONLY needed for slicers which are using different pivotcaches, if they share the pivot cache, tie them together using the Pivottable connections of the slicers.
Sorry, me again...
Your code is working with one of th eslicers but not with the other 2.
I have 3 slicers per each datasource associated to several pivot tables.
Each slicer have the same caption name for each data source
-Months
-Station
-Country
For the month slicer the name "Months" is in allat the same place
For the station one, the slicer caché are the following:
Slicer_Pos_Station_Code / Slicer_Origin_City_Code and Slicer_Origin_City_Code1
For the country:
Slicer_Pos_Country_label / Slicer_Origin_Country_Code and Slicer_Origin_Country_Code1.
I think that it is where I have an issue and the code is not working.
I have tried to manage it in the Mid function and also adding a "or" function to include the City name.
Can you help me adapting your code?
many thanks in advance
How to get slicers to stay in one place on the sheet and not move when filters are selected and the pivot table below changes size,eg, has less columns
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
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?
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?
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:
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?
Sorry to bother again, I fix the issue I posted on the 25th of June by creating the same field names for each of the data sources. Your macro works very well and I thank you for that.
But there is always a but (I am still not good enough to make big changes by myself) recalculation takes 2 long if I base this macro on the whole work book (the data sources sum more than 350 MB) so my idea is to split this macro in one for the 1st worksheet that needs the synchronization and in another one for the other.
The first work sheet name is equ and the other one is equ HEA
how shall I write this in the macro instead of "Thisworkbook"?
I suppose doing this the synchronization will take place only for the worksheet I am working on true?
Thanks again for your help
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
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.
It depends. A Slicer can be placed on ANY worksheet, not necessarily on the same sheet that the pivottable the slicer controls is on. Also, one slicer can control multiple pivottables on multiple worksheets. So what the code needs to do precisely, depends on your workbook setup and your requirements.
Can you do this for Timeline Slicer? I was able to do it for regular slicers, but not Timeline Slicer, with all the parameters you described consistent.
Is there a known problem using slicers for pivot tables with calculated items? Quick google search didn't reveal anything.
I have 3 slicers. Usually if I choose a value from the 1st slicer; the 2nd and 3rd change such that items with no data are shaded out (per the rules of the style). This was working but as soon as I added a calculated field it believes that there is always data and hence it is not greyed out.
If trying to create a slicer that will expand and reduce columns through a horizontal filter... is that possible? Slicers usually function as vertical filters, but what if I want a slicer that will specifically select the headers that will be in my pivot? Essentially this would be a horizontal filter on the column headings. Is that possible without using power pivots?
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
Re my previous, I want to remove the fill of the box thatsurrounding the slicers. I am using a photographic background for the page and want just the slicer buttons to appear (no visible box (fill) around them. How do I get a transparent option?
Many thanks for the effort in writing this code. It perfectly suits my case, but doesn't SOLVE it.
I tried 5-6 macros (went deeply into Googling) that would solve my case, but non of them could synchronize / mimic / mirror the selected items on the Slicer.
Just a few facts:
- 1 data-set responsible for 3 pivots (and 2 pivot charts from those), another data-set responsible for another pivot
-1 data-set contains weekly values, second data-set - monthly
- both contain IDENTICAL parameters as Forecast Period, Country, Location, and Product
- slicercaches names are for example: Slicer_Forecast_Period, and Slicer_Forecast_Period1
Assuming that everything is the SAME I should be able to have only 1 slicer instead of 2.
Finally, the code provided by you was adjusted to suit my case. And I'm not new to VBA, got some basic experience.
Please ignore my previous message / comment. I made some progress and macro is now triggered. However, I'm getting an error "Method 'Selected' of object 'SlicerItem' failed" on the line
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True 'error here
For Each oSi In oScForecast.SlicerItems
Re my previous, I want to remove the fill of the box thatsurrounding the slicers. I am using a photographic background for the page and want just the slicer buttons to appear (no visible box (fill) around them. How do I get a transparent option?
Set Data_sht = ThisWorkbook.Worksheets("Sheet5")
Set Pivot_sht = ThisWorkbook.Worksheets("Sheet12")
Set Pivot_sht1 = ThisWorkbook.Worksheets("Sheet11")
Set Pivot_sht2 = ThisWorkbook.Worksheets("Sheet13")
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
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
Great code, I was searching for this for days, but the UDF does not work with XP SP3 and Excel 2010, it gives runtime error 1004. However, I got it to work by adding an extra object:
Dim osc2 as Object
Set osc2 = osc.SlicerCachelevels(1)
For each oSi In osc2.SlicerItems
If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & TrimSlicerItem(oSi.Name) & ", "
lCt = lCt + 1
End If
..
and If lCt = osc2.SlicerItems.Count then
TrimSlicerItem extract the 'data' from the output that looks like this: [table].[column].&[data] and the else clause had to be deleted to count lCt correctly in my test.
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
Also wanted to say fantastic solution to this problem.
However, is it possible to get this to work with multiple slicers? I have four slicers and I'd like each one to have its selected slicers appear in a different cell.
I've managed to have all four slicers show their selected items in a single cell but this looks cluttered and awkward.
Here is an alternative version which has an extra argument with which you can choose to either get all items in a single cell, or return a list of them. For the latter to work you must array-enter the formula into as many cells as you expect to have sliceritems.
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Thanks for the amazingly fast reply! One thing I didn't realize in your original code is that SlicerName is not supposed to be replaced (I put the name of one of my slicers in there). Sussed that out and everything works great with your modification. Thank you so much!
Do you know of a way to sync a timeline slicer with another slicer. I have a pivot table that has dates and locations. Not all locations are used in each month range which is why I was wondering if it is possible for only the locations valid for the selected month will appear in the slicer.
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).
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
Thanks so much for your explanation. It is been very exciting to learn all about slicers.
I have a problem that should be simple.
I have 4 slicers connected to the same pivot table and would like to pass on to GetSelectedSlicerItems only the slicer that actually had an item selected.
How can i do this? Is there any how to find out which of the slicers triggered the Workbook_SheetPivotTableUpdate?
I'm afraid there is no simple way to detect which slicer was clicked. The only way I would know is to keep score of which filtes were applied and then try to derive the clicked slicer from the newly applied filters. Given that a click on one slicer may als affect what is displayed on another slicer, this is not easy at all.
I tried following of your macro's:
Public Function GetSelectedSlicerItems... etc,
and that works as expected. BUT my issue is that it only works if I do the selection in the slicer itself. Meaning that the =GetSelectedSlicerItems does not give the result when the sliceritems are reduced as impact from slections in other slicers.
Any suggestions?
CHeck out this version, which has an additional parameter that you can set to true or false to either ignore or not ignore items that other slicers have filtered out:
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Jan,
Do you also have a trick on how to load that list of "GetSelectedSlicerItems" into another slicer (to synzhronize two slicers with different data sources)?
It's just a long shot as that would save me a lot of time.
Thanks in advance, JHN_DK
Hi Jan - I read through all the comments, appreciating the effort you put into helping people! However, I don't see this problem:
I have slicers on a Pivot Chart (Power Pivot actually) in Excel 2010. I was able to format the width of buttons, number of columns, position on the sheet, etc on some of the slicers at some time, but not all of them. Today, two of the 3 slicers will not behave - I can't move them nor resize them - they keep autoformatting to an unacceptable appearance.
Any thoughts?
thanks a bunch
Thats exactly I was looking for.... but I found an issue. It Works perfectly in a pivot table of a local data base in Excel, but when I tried to do it in another pivot conected to an SQL Data Base through an ODBC conection in Power Pivot shows the follow message: "Not Ítem Selected"
SLicers tied to Powerpivot pivottables or to the Excel 2013/2016 datamodel behave different. I have made a modification to the function which now should work for both cases:
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Hi, I've found the issue regarding the drilldown not being filtered when using a slicer. The field used in the slicer must either be a part of the pivot table or a filter on the pivot table for it to work. If it isn't in the pivot table either as a field or filter, the filter will not work and will show all of the data instead. I have 8 different slicers tied to several pivot table and having the filters showing on all the pivot tables just makes it confusing. Is there a way to work around this?
Thanks for looking into Jan. That was the solution I went with. It's too bad Microsoft hasn't seen fit to tie the drill to the filters applied in the segment. It's a little confusing for users seeing a lot more data than shown in the pivot table when they drill down.
This is an excellent site. It has given answer to a problem I have been searching for a couple of days now.
I need a help to create an ACCOUNTABILITY DASHBOARD for a health organization.
This dasboard is intended to tack accountability of officers who have been given responsibilities to accomplish. The proposed indicators for the dashboard are as follows:
ACTIVITY TYPE, PROBLEMS REPORTED, REPORTED BY, REPORTED TO, DECISIONS TAKEN, PERSON RESPONSIBLE FOR ACTION, TIMELINE, ACTION TAKEN/NOT.
Please I would like to know if combos could be created for the slicers to select the options (if so how?)
instead of the default which displays all the options on the slicers occupying space.
It is a bit hard to advise without an idea about the data.
Perhaps you'd better ask this question at http://www.eileenslounge.com as there you can upload example files and have more people available for help.
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
I think I'm encountering the same problem as T. (5/22/2012 2:30:49 PM)but having read the whole forum I couldn't find the answer. Let me present it my way.
I use Excel 2010 Reports filtered by several slicers to then create graphics off a PivotTable located in Access 2010.
When some figures in the graphics are unexpected, I'd like to go to the underlying report double click on these figures to get the pivot table entries the the number is based.
Unfortunately, when I do that I get a new sheet with a list of entries that is much larger than what is synthesized by the number I was investigating. I'm tempted to say that the list on that sheet does not take into consideration the slicers. Would you by any chance have a way around that ?
I ' be very thankful.
Thank You for this great reference page
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.
If a slicer filters a field that is NOT in the pivot table, the filter settings of that slicer are ignored when you drill down. The only way around that I know of is either by adding that field to the PT (as a page field perhaps) or by using a VBA event to filter the data after the drilldown.
Indeed, coupling the slicer with the report gives you the numbers you want BUT if you want the drill down to be coherent with that number you must take care to include in addition all "sliced" fields at least as a pages to the report you're "drilling".
Works great ! Thanks again
Ik heb een andere aanpak gekozen voor het probleem: "Approach is by saving state of all slicer cache objects. After a change in a pivot table new states can be compared with old states and a list of updated caches composed. Once this list is known, synchronizing can be accomplished."
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.
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
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
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
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
i am using excel 2010- Microsoft professional plus, and when i click on the slicer , after selecting any cell from a table, it opens a dialog for existing connection . how can i solve this ?
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
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..
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?
This sounds like it is what I need, but I am not quite sure. I am just beginning to learn VBA, had some experience working with a program Boston Workstation that utilized some VBA, basically an interface that helped you build VBA scripts easier, but not experienced. Anyway, my issue is that I have a dashboard that is summarizing data the is pulled from multiple different pivot tables via the getpivotdata function. I have two caches, because I have to isolate some of the data from the original data, all the same data fields, just a subset with specific sorting and duplicates removed etc. I have two slicer's "Campus" and "PRIMARY_PAYOR" that all I want to do, is make the slicer's mirror each other, as in, if I select a location on the "Campus" slicer that is on the dashboard, the second campus slicer that is hidden on another tab is also changed to that location, that way, all the data showing on the dashboard is for that location, and same with the payor. Can you help me adapt this code for my purposes?
That is precisely what the code example is for. So yes that should be possible. What you need to know is the name of both slicers.
Right-click the slicer and select Slicer settings. Record what is written next to "Name to use in formulas" and use that in the code.
I used this code to manage 6 slicercaches with two slicers and it works great.
There is one thing that is not working I guess: event looping.
In my case the code is run 7 times where after the first run all slicercaches/pivots are already set to the filtering.
I think it is due to having on one slicercache a third slicer (3rd level of detail only available in one pivot data set) but I can't get it to work.
Any ideas?
You should be able to prevent event looping by using Application.EnableEvents = False in the beginning of the event sub. Don't forget to turn it back on at the end!
I tried that but didn't work. The events are executed after the code Sub Workbook_SheetPivotTableUpdate has finished executing. So sequential events.
It seems to be related to the amount of pivots related to a the slicercache of which the slicer changed.
I have a sheet with 7 pivots based on one slicercache. When changing a slicer on that sheet it appears all pivots trigger the Workbook_SheetPivotTableUpdate sequential. On sheets where the slicer is related to one pivot the code is run only once.
In both cases all slicers are synchronized and all pivots are updated.
Ah, yes, of course. One thing you could do is test for Target.Name inside the event and only allow the event code when it equals one pivottable's name.
Ok, that is what I thought, I changed anything that say "year" to the name of my slicer "campus", however it doesn't seem to be executing? I don't get any errors or anything, it just doesn't seem to be calling the event.
I don't understand.
When testing for a valid pivottable's name all the 7 on that sheet will be considered valid and changed so triggering the event.
Or am I missing something.
I guess I solved the 7 pivots on one sheet with one slicercache by only allowing the first pivot on the active sheet to run the event code. Line added to run first in the event:
If Sh.PivotTables(1).Name <> Target.Name Then Exit Sub
I have also a variant where one slicer has two pivots on different sheets. This is not covered by this solution.
I also found a solution for the unwanted trigger when having pivot on different sheets having the same slicer.
Just allow the event to run on the actual sheet where the slicer was triggered.
This conludes in the following code to be included at the beginning of the event:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oScVPCS As SlicerCache
Dim oScTeam As SlicerCache
Dim oSc As SlicerCache
Dim oPT As PivotTable
Dim oSi As SlicerItem
'Prevent event looping on multiple pivots on actual sheet related to one slicer only allow the first pivot
If Sh.PivotTables(1).Name <> Target.Name Then Exit Sub
'Prevent event looping on pivots on more than one sheet related to one slicer only allow the pivot on the actual sheet
If Sh.Name <> ActiveSheet.Name Then Exit Sub
'Prevent event looping, changing a slicer in this routine also triggers this routine
If mbNoEvent Then Exit Sub
mbNoEvent = True
Application.ScreenUpdating = False
.....
...
Maybe you can add this to your example so others can have benefit of it.
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! 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
NewB here. I got the function to return all items. However, it still says "all items" when I click on the selection in the called slicer using : =GetSelectedSlicerItems("Slicer_Response.")
At this point, I have changed no coding. Thoughts?
The reason your code fails is that the function was written with pivot table slicers in mind, not Table slicers. I'll put this on my backburner to add to the article!
When printing a pivot table and slicers, I apply 'fit columns to one page' in the print option for obvious reasons but the slicer font doesn't shrink in proportion with the rest of the scorecard.
Is there anyway of fixing this so that the slicer font is in proportion with the rest of the document?
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.
if I am using the same slicer name with in UDF then I am getting output but that UDF function is giving me coma separated output which I don't want. I need the output in the next Cell instead of coma separated...
I am using Power Pivot and Excel 2016. I wish to have 4 pivot tables linked to several slicers and I think I know how to do that. I need to filter each pivot table to ONE record - all the same - the top record picked by the slicers. I then want to MOVE from first record to next..to last, etc. all while seeing only one record per pivot table.
I know this is strange by I am simulating an existing report that printed takes 1/2 of a page.
Any ideas will be helpful. I like your lessons.
Fred
I love what you've done. I made a modified version, where I pass function arguments for:
* What message to display for "multiple items"
* How many items constitutes "multiple?"
* What message to display for "all items"
It may be that the fault is internal to my cranium - I am getting '#Name' - I have made sure I am using 'the Name to use in formulas' Apologies if this is a no brainer but have tried a few things and am not making progress - any help would be greatly appreciated
If you start entering the slicername in the formula, you should get Excel's intellisense dropdown showing you which slicer caches it has available for you.
I'm getting the same error message as Jake no matter what I enter as the slicer name - either by typing the name in quotes or selecting the name from the dropdown.
This is great! However, when using multiple slicers, if I then go on to choose a slicer within the original selection the cell link refreshes to All Items and my formulas no longer work. Is there a solution for this?
Thanks for your reply. I also thought that should do the trick. But somehow it doesn't work.
I just omitted the name attribute now, so there's no issue with that but the next time I iterate over this line it says "invalid procedure". When I go into debug mode and hit F8 to resume, it says "this slicercache already exists". It actually creates the slicercache but still throws an error.
Apologies, it's quite difficult to explain! I have a file containing costing data to be sent to various contacts but to be protected for their region only.
I'm using your solution in a landing page for the contact to choose their area and enter a password. This then loads the data into a separate tab. However, there are multiple dependent slicers on this tab in which they can delve deeper into the data at site level for example.
However, when they do this the formula on the landing page refreshes to display "All Items" throwing my password and formula off.
Hope that makes sense and really grateful if you have a solution at all!
Hi,
Thank you so much the macro is really helpful.
I use this in one file with 4 slicers and it was great. But I have an issue with the other one.
I have one Pivot based on one source and 8 more based on the other (common for all 8 pivots). I need to add 4 slicers which should work for all these pivots. With this VBA code I synchronized the slicers and they shows the same data but not exactly what I choose. Foe example, if I chooe 2016 as a year it shows the 2016,2015 and 2014. Or just 2016 and 2015. The same is for other slicers.
I'm not very familiar with the VBA so could you please help me with this issue!
Thank you
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
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
I assume your problem is that the data that gets dumped on the new tab is not filtered according to your slicer filters, correct? This happens to fields with slicers that are NOT a field in the pivottable. TO ensure you get filtered drill-down results, make sure your slicer fields are all in the pivottable (add them as page fields for instance).
I want to do the same but for a slightly different case. Instead of synching 3 slicers, I have four pairs of slicers, and I need each pair to be synched.
I have a pivot table with 4 slicers: Type, State, City & ZipCode. I also have a normal data table, not pivot, with the same fields and 4 identical slicers.
I want any selection on the pivot table slicers to be synched in the data table slicers and vice versa.
What would be the altered version of your code for this, given I don't understand VBA at all?
I have a workbook with 3 sheets, "Complaint Chart", "Pivot Tables" and "Pivot Charts".
The actual pivot tables, on the sheet Pivot Tables, are created from a dynamic named range from the Complaint Chart sheet.
I then have 3 slicers, Year, Day and Type on the Pivot Chart sheet which are linked to all my pivot tables.
The problem I have is when the userform adds new data to the Complaint Chart sheet the pivot tables refresh ok but the slicers appear to duplicate the 'filter options'. For example, The complaint chart sheet contains many rows containing the year 2016, however when new data is added to the complaint chart, with year 2016, the pivot tables refresh ok but the slicers then show 2016 twice in the 'filter options'.
I cant figure out why this is happening or how to stop it, please can anyone help?
Hi, I have a pivot table with seven slicers and I used the UDF and it worked perfectly for six of the slicers. However, for the 7th one it always says "No items selected" regardless of if I have items selected or not.
Not sure if this matters or not, but that slicer has a lot of items in it (over 10,000).
I agree that slicers are nice but one thing that is truly handy with Pivot Filters is to be able to do a search for filter value. Especially when the list is long this is very handy; this option is not available for slicers I believe.
I have a slicer for years (2016, 2017) but in the list of available selections I also get "<2016-01-01" and ">2017-12-31".
How do I get rid of them? I just need the years. Thank you.
I have inserted a slicer, the resource the slicer is using has multi colored fonts (for categorization purposes). When you double click on the value to induce the slicer information it does not transfer the font color from the original resource. Is there a setting or a way that this can be done?
Whoever wrote this tutorial just saved my last two months of work !! I wanted to synchronize Slicers, but I had this issue of all items selected when looping through the slicers. So I've made a macro running through each pivot filter table to change directly there, but the calculation ended up endless.
Now you have explained the trick of selecting the last slicer item, I'm saved.
Fairly new to the VBA and trying to implement the code for 8 slicers and pivot tables from 2 data sources....My slicers are Brand, Category and Manufacturer....Do i need to use Slicer_Brand, Slicer_Category...in the codes or ust plain Brand, Category ie...also where do i include the names of the pivot tables in the code?
I am getting run time error 91...object variable or with block variable not set error.
Thank you Jan! For some reason when i modify the code and run it I get Run Time Error 1004 and below "oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True" is highlighted in yellow. What may cause this?
The RT error may be caused if your slicer is driving a Powerpivot pivot table or a pivot table based on more than one table. Maybe this suggestion helps:
I guess that is up to you. If items are missing from slicer A, would you select them on B? I'd suggest a workaround where you add a third (hidden?) pivottable which contains all items of both slicers and use a slicer to that table as the main slicer to control the others. The slicers which are possibly incomplete should not be visible to the use IMO. To make it transparent to the user, I recommend to add visual clues (on the cell grid) if items are not available in a pivot table which do show on the slicer.
from you initial post
"If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer."
well, it's EXACTLY what I need.
assuming I have everything on the same data set (a column for "quarter" e another for "month") is it doable?
I just want to be able to set the month slicer and the quarter slicer adjusts automaticaly.
Maybe you can point me in the right direction as I could not find it online for a VBA (very) newbie.
If the pivottables are connected to the same pivotcache, all you need to do is add both Q and M slicers and tie them to all pivottables. They'll sync automatically without need of VBA
you ALMOST solved my problem.
I made 2 pivotables, one for quarter, one for month
2 slicers, one for quarter, one for month and connected either one to both pivot tables.
it works, but only one way.
if i choose quarter 1, the other slicer will adjust to the corresponding months (but they have to be selected first, or a clean filter)
I would like to be able to choose month 1, or 2 and the quarter would adjust to the corresponding quarter (it does) with the total of the quarter (it doesn't). instead it presentes the same monthly value.
i was testing as i wrote this and found a workaround.
Added an extra column with quarter total for each month line and had that in the pivottable.
problem solved.
Ouch, I apologise, this is indeed not the right solution, as the Month slicer will filter for that month on all attached pivot tables. You would need something like this:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oScMonth As SlicerCache
Dim oScKwrt As SlicerCache
Dim oScYear As SlicerCache
Dim oSc As SlicerCache
' Dim oCell As Range
Dim oPT As PivotTable
Dim oSi As SlicerItem
Dim sYear As String
Dim bUpdate As Boolean
If mbNoEvent Then Exit Sub
mbNoEvent = True
bUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each oSc In ThisWorkbook.SlicerCaches
For Each oPT In oSc.PivotTables
If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
If oSc.Name Like "*Year*" Then
Set oScYear = oSc
ElseIf oSc.Name Like "*Month*" Then
Set oScMonth = oSc
Else
Set oScKwrt = oSc
End If
Exit For
End If
Next
If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
Next
If Not oScYear Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScYear.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScKwrt Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScKwrt.Name, 7, 3) And oSc.Name <> oScKwrt.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScKwrt.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScMonth Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScMonth.Name, 7, 3) And oSc.Name <> oScMonth.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScMonth.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
' ChangeQuarters
mbNoEvent = False
Application.ScreenUpdating = bUpdate
End Sub
Hi.
I can change the font in the slicers. But the "bottums" do not change size. Is there a way to change them as well. The reason why I change the fonts is to have the whole slicer to take less space when printed.
Hi,
I have a slicer that shows different account owners and their department spend. Is it possible to set a passcode similar to one in access, so that when each individual account owner receives this file they can only access their respectful department information?
This is a very good script, but what about if you use the data model as source? I can't get it to work with a pivot table created from PowerPivot. How can you fix that?
To set expectations: I don't expect Geoff is likely to notice your post as I don't send out any notifications to anyone else than the person who posted a comment.
I did try moving it to ThisWorkbook, but it made no difference.
After reading a copious number of blogs on the subject I eventually changed the name to Worksheet_PivotTableUpdate(...) and, hey presto, it work.
The problem then was that I had it calling a function to refresh a Pivot Table, so it got recursively called. Another trail round the blogs and found [B]Application.EnableEvents = True/False[/B] which solved that problem.
So I have now got Slicer2 dynamically showing the items selected by clicking on Slicer1.
Hi, I'm trying to adapt this so that it doesn't show a comma after a single or the last entry, and also that it can show "Multiple selected" if there are more than a given number of selected options, but I'm struggling to get it right. Could somebody help me please?:
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
Select Case lCt
Case Is = oSItems.Count
GetSelectedSlicerItems = "All"
' Case Is > 3
' GetSelectedSlicerItems = "Multiple"
Case Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End Select
Else
GetSelectedSlicerItems = "None selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
To get "multiple items", make sure the Select case looks like:
Select Case lCt
Case Is = oSItems.Count
GetSelectedSlicerItems = "All"
Case Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
Else
If lCt>3 Then
GetSelectedSlicerItems = "Multiple"
'No Else needed, as the string has already been assigned above
End If
End If
End Select
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
Select Case lCt
Case Is = oSItems.Count
GetSelectedSlicerItems = "All"
Case Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
Else
If lCt > 3 Then
GetSelectedSlicerItems = "Multiple"
End If
End If
End Select
Else
GetSelectedSlicerItems = "None selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
...but it seems to make everything multiple (i.e. even if I have only 1 or 2 selected in the slicer)
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
ElseIf lCt > 3 Then
GetSelectedSlicerItems = "Multiple items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Hi Actually it still doesn't work. With CountNoDataItems set to false it doesn't show "Multiple" items if lCt >3, and when set to true it shows "Multiple items" even if only 1 or 2 are selected.
Unfortunately, there is no VBA Event we can use that responds just to changing a filter using a slicer. Nor is there an event that responds to just filtering a table.
So all you can do is things like:
- Use the SheetDeActivate event on worksheets with your slicers and then synch the slicers on that de-activated sheet with the slicers on other sheets
- Use the calculate event to do something similar (but this will likely ony work if formulas point to the tables)
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
ElseIf lCt > 3 Then
GetSelectedSlicerItems = "Multiple items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Hi, I have four slicers linked to a pivot table. I need to count the number of selected items in one slicer, after items from the other slicers have been selected. Is there an easy way to do this? Thanks!
How do I link a slicer to a pivot such that when I click the slicer(it is the first level of row labels as well) the pivot chart should expand to display the next level of row labels?
Slicers only apply filtering to a pivottable, they do not control expansion of pivot items.
You could use VBA, more precise the Workbook_SheetPivotTableUpdate event in ThisWorkbook which gets triggered if you click a slicer and then figure out what to expand.
Job Start Time Complete Time
Script A 00:00:00 00:10:00
Script B 00:05:00 00:06:00
Script C 00:03:00 00:14:00
Script D 02:20:00 03:50:00
I need to be able to slice and display records by time ranges.
For example: Show me all rows where Start Time >= 00:00:00 and Complete Time <= 00:12:00
Note: these time ranges are at the discretion of the user.
Any advice would be much appreciated.
Thanks!!
But I would like to skip the information related to the slicer name in the result, and only show the last part, this being the filtered value in question
[Customer Geography].[Geography Country Name].&[United Kingdom],
My PivotTable and slicer is based upon a data connection to SQL Server Analysis Services Cube structure
I tried running the above function with the slicer names suggested by excel intellisense but evrytime i get the output no slicer with name was found.
Could you please help resolve this problem?
Thank you
The name to be used in formulae is the name suggested by the intellisense. Even using that in the function gives me the same result " no slicer with name was found"
Public Function GetSelectedSlicerItems(SlicerName As String, multiItems As Long, Optional tooMany As String) As Variant
If tooMany = "" Then tooMany = "Multiple Items"
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt >= multiItems Then
GetSelectedSlicerItems = tooMany
ElseIf lCt = oSItems.Count Then
GetSelectedSlicerItems = allItems
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
If Right(GetSelectedSlicerItems, 1) = "," Then GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 1)
GetSelectedSlicerItems = Replace(GetSelectedSlicerItems, ",", ", ")
End Function
A noob here, whatever I do I only get the first value in each and every cell, could you show how the array formula looks for this and what cells does it refer to?
Hi all,
Please advise if there is any option to customize the list of slicer buttons/item? For example, on the slicer I have 3 buttons (Public, Commercial and blank) and I want to see only Public and Commercial. Is there any option to delete/hide a button without changing the original source?
I have the GetSelectedSlicerItems code working. However, How do I get the values of the selected slicers values from the power pivot measures? I am using cubevalue. It returns values for single items. I don't know what formula to use for multiple selected items.
This is the formula I was using.
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[FY18 Budget $]","[Table4].[Allocation Category].[All].["&E$10&"]").
It would pull E10 data if it doesn't include ",". But, I would like to pull data for multiple selected items.
I create a pivot table on excel 2013 and I'm using the slicer option.
First I have 3000 records, I'm sorting by region and I have the following results:
Mexico - 5 results
India - 48
US - 129
When I click on the 5 under Mexico, instead of showing me the 5 records from Mexico it opens the 3000 records. What did I do wrong?
Please help.
You have to select as many cells (n=below each other) as you are expecting selected values. Then you enter your formula into those cells as one array formula by pressing control+shift+enter.
I'm afraid this is hard to trouble-shoot without the data or the file. You can contact me through the email address at the bottom of this page if you like?
Hi Jan Karel. I see that there is a .VisibleSlicerItems property that you can directly iterate over, which will be faster than checking the .visible status of each and every .SlicerItem in the cache.
I'm using this:
Public Function SlicerItems(SlicerName As String, Optional sDelimiter As String = "|") As String
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim i As Long
Dim lVisible As Long
Dim sVisible() As String
On Error Resume Next
Application.Volatile
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
With oSc
If .FilterCleared Then
SlicerItems = "(All)"
Else
lVisible = .VisibleSlicerItems.Count
If .VisibleSlicerItems.Count = 1 Then
SlicerItems = .VisibleSlicerItems(1).Name
Else
ReDim sVisible(1 To lVisible)
For i = 1 To lVisible
sVisible(i) = .VisibleSlicerItems(i).Name
Next i
SlicerItems = Join(sVisible, sDelimiter)
End If
End If
End With
Else
SlicerItems = SlicerName & " not found!"
End If
This code works Great, thank-you! In my slicer I'm filtering Dates, If I wanted the cell result to show a date range instead of each date (ie. 1-Jan-17,2-Jan-17,3-Jan-17) would show as "1-Jan-17 thru 3-Jan-17" would that be possible?
One way I can get Excel to do that is by actually grouping the dates on a fixed number of days.
Another would be to add a column which calculates the "period" from the date column and use that for the slicer.
I have 2 connected Pivot Tables, 2 traditional slicers, and 1 timeline slicer. As it currently stands, the selection in the first slicer will effectively determine what options appear in the second slicer (ie. "Hide data with no value" works correctly). However, when I use the timeline slicer to choose a certain range of time, it does not remove options in the subsequent slicers that would now have no data matching the selected time frame. IS there a way to make the slicers recognize and respond to the timeline slicer's selection?
Hi - I have 4 slicers connected to one pivot table. I would like to show no data in the pivot table (except headers) until a selection is made from one of the slicers. Is there any way to do this without using VBA?
That would be hard to do as a pivot table does not allow you to filter itself to display no data at all to begin with. You can only filter it to show at least one existing category. That is, unless you change the source data of the PT after setting up that filter. If after a refresh of the data no applicable categories remain then your PT ends up without displaying data. So you try to could trick Excel into a state like than somehow.
First of all, your code works fantastic! I do have a question though, is it possible to make it work on a "timeline" slicer? I've used your UDF on the timeline that I have in my sheet but it always returns "No Items Selected" even though it does have items selected. So I'm not sure if it's something I'm doing wrong or what.
I might have missed it but I´m trying to "substitute/control" a slicer with a dropdown. I am working with many pivots that I´d like to filter via a few dropdown´s as multiple slicer´s would be too cumbersome for my use.
A drop-down only allows selection of one item, as I'm sure you are aware of. You could attach a macro to the drop-down, or if you use data validation in a cell (preferred), you could use the worksheet_Change event to trigger a macro that filters the proper field of the pivottable using the content of the cell or drop down. If you then add slicers tied to the same field, those slicers will ensure other pivottables will "receive" the same filter.
im new in this and i trying to select just 1 parameter in my slice, i use .clearmanualfilter and then
With ActiveWorkbook.SlicerCaches("VENDOR_ID")
.SlicerItems("0177797197").Selected = True
For Each slcMiSlicer In ActiveWorkbook.SlicerCaches("VENDOR_ID").SlicerItems
If Not slcMiSlicer.Caption = strActivar Then
If (slcMiSlicer.Selected <> True) Then
slcMiSlicer.Selected = False
End If
End If
Next slcMiSlicer
End With
the problem Vendor_ID hace like 15000 so its take a lot of time
its any other way to clear all and just select 1 faster ? without if.
One thing you can try is adding another pivottable (which you add to the slicer as well!) with just one page field (VENDOR_ID) and then have the sync code write that single value to the cell of the page field directly.
I have an exported data set with additional columns for an individuals name and assigned location added at the beginning.
I would like to be able to utilize the slicer to have all individuals at the sliced location to show even if they have no data to count.
What is happening is I can either get all individuals to show (regardless of the sliced location). Or I can get only the individuals with data to show (sliced location works here).
Is there any way to get all data to show only from the sliced location?
I'm not sure I can give a sample due to confidentiality involving names and such.
What I can tell you is that there are 4 linked pivot tables from 4 separate data sets (monthly, quarterly, semi-annual, and annual). These are counting entries of a particular test performed in the specified timeframe. Each of these sheets have the names and work locations of individuals in the added columns.
I have added a slicer and reported relationships to the other tables. It is set to control the 4 pivot tables. However when the slicer is clicked for a particular location I see the following:
* With the Field setting showing items with zero data, I get all employees from all locations and only the test count from the selected location.
* With the Field setting NOT showing items with zero data, I only get the employees from the selected location with the count of tests entered.
What I would like to accomplish is to have the show items with zero data, but only from the location selected.
I have a hard time picturing your data but I do suspect what you need is not possible unless you change your data layout to one which has a separate table with people which is joined by adding a relationship.
I have a Slicer that is a custom Calendar by Week. Works fine. However, for example we have February 2018 data that is “not ready” yet for the end user to view. Since there is data thought in the pivot tables the February 2018 are available for selection by the end user. Is there a way via VBA coding to hide or grey out this selection. For example they could only select the January 2018 slicers. We then would need to modify this code each month.
Great solution, however after setting it up to get the results from 12 slicers, it runs extremely slow. Sometimes it's taking over a minute to display the data.
Any idea why I would be getting #NAME? when I put the function call in a cell. I know that it means that it can't find that name, but I have triple checked to see that I'm using the right name in the call. I have slicers in tables and in pivot tables, and get the same response in both cases. I do use sql to get the original data, but then copy/paste it into Table1 and then drive pivot tables of that table, so no powerpivot (yet).
I was referring to the situation where you are typing the formula directly into the cell. If you cannot get it to work, feel free to email a copy of the file to me. Make sure you refer to this conversation and (in the file) highlight where you are trying to achieve what.
Hmm, good point, this timing popup should be an optional one indeed. I'll add it to the wish-list.
Having thought about your process I think additing a new menu item to add reports to multiple workbooks is perhaps a good idea. What features would you expect such functionality to have?
Need help. I have 3 slicers in my dashboard, 1 filtering the pivot charts and other 2, non-pivot-tables. All 3 of those have different data sources and data, just a "city" column in common with the same cities.
How can i use vba code to connect other 2 slicers to my 1st slicer.
I pulled this off my adding this code to the ThisWorkbook module:
Option Explicit
Public NoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim sSelected() As String
Dim lCt As Long
Dim oScPT As SlicerCache
Dim oSi As SlicerItem
ReDim sSelected(1 To 1)
Set oScPT = ThisWorkbook.SlicerCaches("Slicer_City")
If NoEvents Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
NoEvents = True
For Each oSi In oScPT.SlicerCacheLevels(1).SlicerItems
If oSi.Selected Then
lCt = lCt + 1
ReDim Preserve sSelected(1 To lCt)
sSelected(lCt) = oSi.Value
End If
Next
SyncSlicer sSelected, "Slicer_City1"
SyncSlicer sSelected, "Slicer_City2"
NoEvents = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub SyncSlicer(sSelected() As String, sSlicerName As String)
Dim oScTable As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
Dim bShow As Boolean
Set oScTable = ThisWorkbook.SlicerCaches(sSlicerName)
For Each oSi In oScTable.SlicerItems
oScTable.SlicerItems(sSelected(1)).Selected = True
bShow = False
For lCt = 1 To UBound(sSelected)
If oSi.Name = sSelected(lCt) Then
bShow = True
Exit For
End If
Next
If oSi.Selected <> bShow Then
oSi.Selected = bShow
End If
Next
End Sub
Jan Karel Pieterse-
Thank you so much for all this information!!!
I found the answer to a question I had about how to access a slicer item when you are connected to a cube. I had been searching and searching. Thank you so much!!!
Best regards,
Tad Hammer
I'm getting a return of "no slicer found with name". I've searched the rest of the comments but can't find the solution here.
I enter the slicer name with quotes:
"Slicer_Woord1" as shown in the slicer settings name used for formula.
I've used a normal powerpivot table.
How can I fix this?
It should work. Have you tried entering one of the CUBE functions, these take a slicer as their argument and the formula intellisense knows their names.
I've tried using the CUBE functions, they don't work either. When I start typing excel does recognize the name "Slicer_Woord1", but my data is not in a PowerPivot model so the CUBE functions won't work right?
I rarely use Excel, so I'm sorry if this sounds stupid. I've manually entered the data in Excel then I made a normal table from that data. I want to use this data to make a dashboard so from the normal table I clicked on summarize with Pivot-Table. The new Pivot Table has a Slicer. From that slicer I want to show the selecter words.
It's a good solution and i have used it, However it consumes a lot of Excel & Pc resources since it is a Volatil application as declared in the code itself. This means that the macro will be recalculated whenever calculation occurs in any cells on the worksheet, making the computer slow.
After many attemps i've found three methods that gets slicer selected items name. Firts one works based on Slicer using a macro (Not a UDF), Second works upon pivotTable directly and the thir one works with some formulas and extra entries in worksheet cells
I know this is a long commented post but if someone is interested just let me know and I'll share
I wanted to inquire about Miguel three solutions to your code. With the code being very calculation intensive, I wanted to explore other possible solutions.
Hi there, when I try to edit the font size of my slider using your technique above of duplicating and then pressing modify, the modify button doesn't take me anywhere? It just remains on the same page?
My spreadsheet default font is rather large so the slicers are fairly useless if I can't edit them! Can't see anywhere online where this has happened to anyone else?
Using a Mac Pro - and when I press the modify button it just highlights blue and nothing happens.
Thank you for sharing this post. I have used it in 1 of my dashboards to insert graph title. I copied the UDF into a new workbook with just 1 pivot table and 1 slicer. When I past the formula into a sheet cell I get a #NAME error. I changed the slicer name. Any ideas to what I might be doing wrong?
I inserted in a new module. It resolved by closing the excel files and rebooting. I think I had 2 workbooks open. I will monitor the situation. Thank you for sharing such a great code.
Kerry
I would want to count the number of items that are selected in a slicer based on the selection of the other filter. the UDF simply displays the name that I have selected not the # of items that have been selected. would you know how I would get the just the # of items within the filter/slicer.
Public Function GetSelectedSlicerCount(SlicerName As String)
Dim oSc As SlicerCache
Dim oSi As SlicerItem
On Error Resume Next
Application.Volatile
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected Then
GetSelectedSlicerCount = GetSelectedSlicerCount + 1
End If
Next
Else
GetSelectedSlicerCount = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Thank you Jan for quick reply. It does count the items within the slicer. Here are the two issues. One, it doesn't change based on the other slicer selection. two, it counts all the items and not the items that got changed based on the others slicer selection. Basically I would want to show the grand total in a pivot table in a cell. As i change the filters , grand total changes. I am using slicer to do the same thing. any help would be really helpful.
First, THANK YOU for the function to return Slicer Selections. This code has been ESSENTIAL for a number of projects over the last year or so!
My question now... I have a lengthy staff list and when the user "de-selects" a staff member, I do not want to display the 87 people _selected_ but only indicated which person is NOT selected.
Ah! nevermind! I just wrote in asking about NON SELECTED... but then looked more closely at your awesome code and it was trivial to edit! I include here in case you'd want to correct me somehow... :)
THIS IS AWESOME!!!!
Public Function GetNonSelectedSlicerItems(SlicerName As String) As String
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected = False Then
GetNonSelectedSlicerItems = GetNonSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
End If
Next
If Len(GetNonSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetNonSelectedSlicerItems = "All items omitted"
Else
GetNonSelectedSlicerItems = Left(GetNonSelectedSlicerItems, Len(GetNonSelectedSlicerItems) - 2)
End If
Else
GetNonSelectedSlicerItems = "No items omitted"
End If
Else
GetNonSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
And now... because this UDF is Volatile (and therefore slowing down my workbook), I wanted to make this into a SUB instead of a function... so now I can click a button to run this sub and return the selected items:
Sub ReturnSelectedSlicerItems(SlicerName As String, wsName As String, OutputCell As String)
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
Dim GetSelectedSlicerItems As String
On Error Resume Next
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems = "All items selected"
Else
GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
I am not sure if the above is what I am looking for but I am looking for a solution for the following problem.
I have a table with a number of columns. One of the columns contains the region where a specific task is being executed. An other column also contains the region where a follow-up task is being executed. Both regions can differ from each other.
Both colums have obviously their own slicer.
What I would like to achieve is that whenever the first slicer is being activated with a region the second slicer gets the same setting. So both slicers in the example below would be set to "Midden" by just clicking on the first slicer.
With ActiveWorkbook.SlicerCaches("Slicer_RegioKantoorBehandelaar")
.SlicerItems("Midden").Selected = True
.SlicerItems("").Selected = False
.SlicerItems("Noord Oost").Selected = False
.SlicerItems("Noord West").Selected = False
.SlicerItems("West").Selected = False
.SlicerItems("Zuid").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_RegioKantoorOpnieuwBehandelaar1")
.SlicerItems("Midden").Selected = True
.SlicerItems("").Selected = False
.SlicerItems("Noord Oost").Selected = False
.SlicerItems("Noord West").Selected = False
.SlicerItems("West").Selected = False
.SlicerItems("Zuid").Selected = False
End With
I took a good look at your code, adjusted it to work in my sheet and I am impressed. Works great. This is indeed was I was looking for.
However clicking on one slicer and synching the matching other slicer takes up to 27-40 seconds depending on which Region in the slicer was selected.
What could be causing this huge amount of time. I have
- About 9000 records, 83 columns
- 11 slicers
- Only 2 slicers are synched with your script
- Dozens of PT's
- All combined in a dashboard
I'm not sure. But each change in filter will cause an update of all of the attached pivottables, so it'd make sense if it would take a while if there are many pivottables attached to the two slicers.
This could probably be better solved by making sure there is only one slicer to filter on rather than having to synch two!
I have a table with 2 slicers and 2 pivot tables with slicers
The 2 pivot tables along with 3 others(these 3 do not have slicers) count items from 2 columns in the original table and then display as graphs
I want the 2 slicers from the table labelled Region and Country to link to the 2 pivot table slicers of the same name
The pivot table slicers are called Slicer_Region and Slicer_Country
The table slicers are called Slicer_Region1 and Slicer_Country1
I have created a setup where a pivottable has a slicer named Slicer_Shipcountry1 and a table has a slicer named Slicer_Shipcountry2. To have the PT slicer update the table slicer:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oSl As SlicerItem
Dim oSc1 As SlicerCache
Dim oSc2 As SlicerCache
Dim sSheet1 As String
Dim sSheet2 As String
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
Set oSc1 = SlicerCaches("Slicer_Shipcountry1")
Set oSc2 = SlicerCaches("Slicer_Shipcountry2")
mbNoEvents = True
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
mbNoEvents = False
End Sub
Best to hide the table slicer (no code responds to changes on a table slicer) and just copy the pivot table slicer to the sheet where the table is if you need the slicer to also be available from there.
I assume if there are multiple slicers on each table type, it would just be more variables and the following code repeated with the new variables, oSc3, oSc4, oSc5, etc
Set oSc1 = SlicerCaches("Slicer_Shipcountry1")
Set oSc2 = SlicerCaches("Slicer_Shipcountry2")
mbNoEvents = True
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
Yes that would work. Alternatively you could move the code out of the Pivot event into a normal module, adding the slicernames as arguments. Then you could pass the slicernames as arguments to the new sub.
So the heading of the new sub would look like:
Sub Synch2Slicers(oSc1 As SlicerCache, oSc2 As SlicerCache)
Dim oSl As SlicerItem
Dim sFirstSlicerItemText As String
Dim lCt As Long
Dim bFound As Boolean
On Error Resume Next 'In case a slicer item in slicer 1 is not present in slicer 2
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
End Sub
ThisWorkbook would then change to a simpler:
Option Explicit
Dim mbNoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
If mbNoEvents Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Synch2Slicers SlicerCaches("Slicer_Shipcountry1"), SlicerCaches("Slicer_Shipcountry2")
'Add more of those above if needed, just make sure you pair the right slicer names in the correct order
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
mbNoEvents = False
End Sub
This code is great when the slicer is linked to a PivotTable. But when the slicer is linked to a Table, the function returns "No slicer with name 'Slicer_ProposalType' was found", where Slicer_ProposalType is the name of my slicer. Is there a way to get it to work with a slicer that is linked to a table?
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Your last piece of code works great with the table I've assigned to my pivot. Although it brings back [table].[column].[Data] . Is it possible only to show Data in the returned cell ? ..
I have multiple slicers, and some of them have (blank) values depending on what other slicers are set to. When that happens i get part of the slicer name as the result. How can I get it to say (blank) instead? Here's the resulting text as an example: recordstatus].&,
The current code I'm using:
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
GetSelectedSlicerItems = GetSelectedSlicerItems & Split(oSi.Name, "[")(UBound(Split(oSi.Name, "["))) & ","
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False, _
Optional CountNoDataItems As Boolean = False) As Variant
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim oSItems As SlicerItems
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
Err.Clear
Set oSItems = oSc.SlicerCacheLevels(1).SlicerItems
If Err.Number > 0 Then
Set oSItems = oSc.SlicerItems
End If
For Each oSi In oSItems
If oSi.Selected And (oSi.HasData Or CountNoDataItems) Then
If oSi.Name Like "*.&" Then
GetSelectedSlicerItems = GetSelectedSlicerItems & "(Blank),"
Else
GetSelectedSlicerItems = GetSelectedSlicerItems & Replace(Split(oSi.Name, "[")(UBound(Split(oSi.Name, "["))) & ",", "]", "")
End If
lCt = lCt + 1
ElseIf oSi.HasData = False And CountNoDataItems Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
If ReturnArray Then
GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ","))
End If
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
I found a workaround for getting the information off of a timeline slicer. This will be able to give you the minimum & maximum dates used on the slicer to be able to reference in cells or equations.
Thank you for all your solutions to complex problems. I have a worksheet setup to hold all slicers in one place which update quite a lot of number pivot tables. I had the same problem with performance. I use a routine on the pivot cache with the master slicers to turn off/on ManualUpdate on all pivot tables in the workbook. Performance is acceptable now. I also show the user that the report is being updated with a form.
Sub PivotTableManualUpdate(bSwitch As Boolean)
Dim oPt As PivotTable
Dim oWb As Workbook
Dim oWs As Worksheet
Set oWb = ActiveWorkbook
For Each oWs In oWb.Worksheets
For Each oPt In oWs.PivotTables
oPt.ManualUpdate = bSwitch
Next oPt
Next oWs
End Sub
Hope this can help anyeone with performance problems.
Is there any way I can select only specific items in a slicer? For example, let's say that there is a slicer for "City" and I only want to select cities with a population of more than 500 (assume the population is also part of the data). Can I write a VBA code to select just those cities?
Apologies for the belated reply.
A slicerItem in a slicer object has a HasData property which is false if that item of the slicer would cause the pivottable to be empty when selected. To use that information, you must first filter your pivottable on the "population more than 500" condition.
I used the VBA code and it seems to answer "No items selected" even though some items are selected. The excell worksheet does contain a Power Pivot link, so could this be the reason it doesn't work?
Is there a way to disable right click on slicers? I don’t want end user to manipulate my data, specially the “report connection” . Please help me solve this..thank you!!
I tried to do that, but I failed. If you protect the sheet and set the slicer to locked, the slicer no longer works. The only way to achieve this is by adding ribbon customisation to the workbook and disabling the commands you don't want to allow access to. Even then, I bet there is still a way to get at what the user wants to do.
These things are hard to protect a user from doing. What you might do is add some VBA that checks the slicer settings and restores them to what you want them to be.
How about, disabling report connections... on context menu? I am a newbie and I dont know how to do it..please help me, I appreciate your reply, thank you so much!! You helped me a lot too with the project I’m currently working on
Ik heb een Performance Dashboard gemaakt waarbij ik meerdere draaitabellen heb gekoppeld aan 1 slicer. Nu heb ik gisteren een extra draaitabel gemaakt, welke ik ook aan dezelfde slicer wil koppelen. Echter komt deze 'draaitabel 7' niet bij de rapportverbindingen te staan in de slicer. Weet jij hoe ik deze draaitabel toch kan koppelen deze ene slicer?
Is it possible to group slicer values into buckets/bins, without creating additional columns in the source data? I have a slicer with values or 0-100. Instead of having 101 numbers in the slicer, I would like to be able to select 0-9, 10-19, 20-29, etc.
Any ideas are appreciated!
Thanks!
I'm not entirely sure what you mean. DO you have a table which you've transposed using the From table button on the Data tab? If so, the answer is yes!
I have 2 Queries (Ole DB) that I use to create 1 pivot table each (PivotTable1 & PivotTable2) on the same worksheet (ptForecast), with each pivot table having 1 slicer for 'Department' (Slicer_Department and Slicer_Dept), so it has 2 separate pivot caches. The items in the 2 slicers for Department/Dept have the same list of items.
I would like to control both PivotTables with one slicer.
I have tried numerous variations of the code you initially provided and others have asked about in the comments, but so far haven't been able to come up with a solution that updates Slicer_Dept with Slicer_Department's item.
I've had a degree of success using this this code to show slicer selections. However, it doesn't quite suit my needs. I'm looking for a method to display only items selected or deselected, i.e. show me what the user has clicked on. Using the mentioned code, if I deselect an item using control-click it displays all other selections - not ideal when deselecting one item from hundreds.
Any suggestions?
Hi Jan,
first of all: a great thank you for this piece of work. It really helps a lot. But is there a possibility to "autoselect" displayed sclicer items? E.g. you have a dependency by selecting "Europe" and your ShipCountry Slicer would only display "Austria, Belgium, France..." but not the others. Now I want to select those displayed automatically. Thanks in advance.
I think you want the slicer to turn off its filter when the selection changes of another slicer? So for example: if the another country is chosen, the filter from cities is removed?
that is not the problem. I wanted to select the displayed items automatically, if the filter changed. But the good slicers in Excel are, the bad are the possibilites in VBA. I solved this another way with a PivotTableUpdate event.
I use and love this function, thank you; however, I would like to know how to custom sort when multiple items are selected.
For my current situation, user will select between 1 and 7 items. Other tables may be between 1 and 4, 1 and 15 items...but again, for today 1 and 7 items.
Hi, I'm a trainee student of Chem. Engineering, I've never thought I was going to need this kind of VBA. I found the link to this topic in a Youtube comment, and I'm so glad for that.
You helped so much to me with this, and I just want to say thank you so much.
I have an issue with my slicers. Basically I have a dashboard with 5 graphs coming from 5 Pivot Tables. 4 of them come from the same base table, but one comes from a different one. I don't think it's possible to combine them. Both tables have a dataset in common: area code. I would like that when I select a number in my slicer 1, the slicer 2 selects the same one automatically.
I'd say "it depends". If you use the data model for the pivot tables, you can add an in-between table which contains all unique area codes. If you then set the correct relationships between the tables you should be able to use the new area code in both your pivot tables and slicer to filter then with one slicer. Clear as mud?
Thank you for this function. I would like to know how to do the opposite of what's being achieved. How do I change it to show the items that have been filtered out?
I have used this solution for several years in a file that I keep, but this week I started to get some issues and was hoping you could help.
I have the formula referencing my Slicer name
=GetSelectedSlicerItems("Slicer_Fund_Name) but when I enable the document, I get an error in the VBA debugger that says :
Compile Error: Cant Find Project or Library and its highlighting the Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) section.
If I have a table of accounting transactions for 12 months data in a pivot table
Is it possible to create a slicer that displays a column with the total for month 3 and a second column with the total year to date [ie ytd]
Slicers can only contain the items from an existing field (column) of the source data of your pivot table. Displaying (sub)totals in a slicer is not possible.
1.) Is it possible to get the filter / chosen duration of these time span slicers too? E.g. Jan2021 to Aug2021
2.) Is it possible to get the results in individual rows, e.g. by using that function as a kind of array formula or any other kind? So to see the filter values not comma separated but each in a single line?
I am trying to find a way to limit a slicer to single select. I need to Disable the multi select and CTRL button so the users cannot circumvent the Single Select as the ONLY select.
I have a Data Entity that needs the single select and it filters 3 other slicers in turn. Is there any way to limit the slicer as a single select, or maybe use a single select drop down that will filter the slicers? With or without VB code.
There is no way to limit a slicer to just one item, But what you can do is this:
- Add a pivottable to the same slicer in some out-of-the-way location which has the slicer field as a row item
- Write a formula to count the # of filtered rows
- Use a formula in a cell next to the slicer that displays red text as soon as more than one item is selected in the slicer.
Is there a way to dynamically get a slicer name to use in a CUBE formaula?
I have a template sheet that I want to duplicate "x" number of times in a single file. The slicer name is used for CUBE formulas. When the tab is duplicated, the slicer name changes so the formulas return the values on the original Template sheet, not the duplicated one.
I don't think there is an easy way to do this, you must edit the formula to use the correct slicer name. You can however move the slicer name to a separate cell on the sheet. In order to make it work, the cell must contain a formula with just the name of the slicer preceded by the = sign. For example:
=Slicer_Country
But there is no way to make that formula dynamic by building a string that appends a number to the name, you must edit the formula in that helper cell for every worksheet.
Slicers can be tied to tables (ListObjects) but 1 slicer operates only on 1 such table. It would be very useful if 1 slicer can filter in several tables, simply in Excel.
How do you modify the below code to connect another slicer?
Option Explicit
Public NoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim sSelected() As String
Dim lCt As Long
Dim oScPT As SlicerCache
Dim oSi As SlicerItem
ReDim sSelected(1 To 1)
Set oScPT = ThisWorkbook.SlicerCaches("Slicer_City")
If NoEvents Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
NoEvents = True
For Each oSi In oScPT.SlicerCacheLevels(1).SlicerItems
If oSi.Selected Then
lCt = lCt + 1
ReDim Preserve sSelected(1 To lCt)
sSelected(lCt) = oSi.Value
End If
Next
SyncSlicer sSelected, "Slicer_City1"
SyncSlicer sSelected, "Slicer_City2"
NoEvents = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub SyncSlicer(sSelected() As String, sSlicerName As String)
Dim oScTable As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
Dim bShow As Boolean
Set oScTable = ThisWorkbook.SlicerCaches(sSlicerName)
For Each oSi In oScTable.SlicerItems
oScTable.SlicerItems(sSelected(1)).Selected = True
bShow = False
For lCt = 1 To UBound(sSelected)
If oSi.Name = sSelected(lCt) Then
bShow = True
Exit For
End If
Next
If oSi.Selected <> bShow Then
oSi.Selected = bShow
End If
Next
In the code there are two lines which synchronize two other slicers:
SyncSlicer sSelected, "Slicer_City1"
SyncSlicer sSelected, "Slicer_City2"
So if you have slicers you want to synchronize, make sure you have as many lines like these as you have slicers to sync. Make sure to use the right slicer names, you can find those names as I explain here:
Hi there,
Currently i have 4 slicers for 4 pivotables(all with different datasource), i want to control these 4 slicers at the same time. Below are my code, it works for slicer 2 now, but get erorr "invalid call prodedure or aurgument" for slicer 3 and 4, any idea why?
Slicer 1 and 2 are on the same sheet, 3 & 4 are on the other sheet.Thank you so much!
Sub SyncSlicers()
Dim pvt1 As PivotTable
Dim pvt2 As PivotTable
Dim pvt3 As PivotTable
Dim pvt4 As PivotTable
Dim slicer1 As Slicer
Dim slicer2 As Slicer
Dim slicer3 As Slicer
Dim slicer4 As Slicer
' Set the pivot tables
Set pvt1 = Worksheets("Asset_PVT").PivotTables("asset")
Set pvt2 = Worksheets("Asset_PVT").PivotTables("ranked")
Set pvt3 = Worksheets("Liability_PVT").PivotTables("liability")
Set pvt4 = Worksheets("Liability_PVT").PivotTables("ranked2")
' Set the slicers
Set slicer1 = pvt1.Slicers("slicer1")
Set slicer2 = pvt2.Slicers("slicer2")
Set slicer3 = pvt3.Slicers("slicer3")
Set slicer4 = pvt4.Slicers("slicer4")
' Get the unselected items from Slicer1
Dim unselectedItems As New Collection
For Each selecteditem In slicer1.SlicerCache.SlicerItems
If Not selecteditem.Selected Then
unselectedItems.Add selecteditem
End If
Next selecteditem
' Clear selections in Slicer2, Slicer3, and Slicer4
ActiveWorkbook.SlicerCaches("Slicer_F_Country_Name1").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Country_Name").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Country_Name1").ClearManualFilter
' Apply the unselected items to Slicer234
For Each Item In unselectedItems
'slicer2.SlicerCache.SlicerItems(Item.Name).Selected = False
slicer3.SlicerCache.SlicerItems(Item.Name).Selected = False
'slicer4.SlicerCache.SlicerItems(Item.Name).Selected = False
Next Item
End Sub
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.
Comments
All comments about this page:
Comment by: General Ledger (12-1-2011 19:22:29) deeplink to this comment
WOW!! How did you discover all this great stuff? Thanks for revealing these secrets. Please keep this information coming.
GL
Comment by: Vishwanath Nayak (17-1-2011 23:22:11) deeplink to this comment
Dear, I recently purchased a new Dell optiplex 380 with a MS excel non commercial use,, but i dont see this Slicers ribbon/options, tools in my excel while playing with pivots....How should i debug this ?
Regards,
Vn
Comment by: Jan Karel Pieterse (18-1-2011 01:04:14) deeplink to this comment
Hi Vishwanath,
What Office version are you using? SLicers were introduced with Excel 2010 (=Office 2010) and are unavailable in Excel 2007 and older.
If you do have 2010, make sure you select a cell inside a pivot table so that the Pivot tools tab is visible on the ribbon. The slicer button is on that tab.
Comment by: Rick Freitas (9-2-2011 16:28:04) deeplink to this comment
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 (10-2-2011 05:30:12) deeplink to this comment
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: Pradeep (10-2-2011 15:51:34) deeplink to this comment
Hi,
I am using excel 2010 but can not find slicer option on ribbon. Evenif i select a cell in piot table it doesnot show slicer option in pivot table tab.
Do i have to download it as and addin.
Pradeep
Comment by: Jan Karel Pieterse (11-2-2011 03:59:08) deeplink to this comment
Hi Pradeep,
It is located in the Sort & FIlter group, on the Options tab of the Pivottable Tools contextual tab of the ribbon. Note that this option is disabled if you are in a pivot table in a file that was saved in Excel 97/2003 format. It has to be a "normal" Excel 2007/2010 format workbook.
Comment by: Guy Brown (3-3-2011 09:52:15) deeplink to this comment
Slicers are the best part of Excel 2010 - especially the ease with which one can connect multiple pivot tables with a single slicer.
Unfortunately there appears to be not backward compatibility with Excel 2007 at all (I'm surprised Microsoft have not come up with a "compatibility pack") - even in xlsm format.
Is there a way of maintaining the pivot connections when opening a "slicered" 2010 workbook in 2007? (though in 2007 I know the old style "check box" filters would have to be used)
Comment by: Jan Karel Pieterse (4-3-2011 02:03:47) deeplink to this comment
Hi Guy,
Not sure what is supposed to happen. The information I had so far was that 2010 objects are ignored by Excel 2007, but still included in a save. So I was expecting them to carry over unscathed. Are you saying they don't?
If so, it may be useful to send me the workbook (the version before 2007 and after 2007 has touched it) so I can issue a bug report.
Comment by: Jason (29-4-2011 07:42:54) deeplink to this comment
I think the question may be slightly misrepresenting the issue. If you have already created a slicer, the Slicers ribbon/options, tools is a contextual tab/ribbon option that shows when you select the slicer itself. It will show up as the right most ribbon option when the slicer is selected.
Comment by: jason (29-4-2011 07:43:50) deeplink to this comment
I'm interested to know what "hovered" means in the slicer element section of of the above image.
Thanks
Comment by: Jan Karel Pieterse (1-5-2011 21:25:50) deeplink to this comment
Hi Jason,
Hovered means the format the slicer item gets when the mouse hovers over it.
Comment by: Jason Parrish (2-5-2011 05:48:16) deeplink to this comment
Thanks Jan.
Comment by: Sebastien (18-5-2011 05:50:45) deeplink to this comment
Hi,
I'm trying to get the selected items from a slicer using VBA.
I can't find how to use:
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 (18-5-2011 06:17:43) deeplink to this comment
Hi Sebastien,
Have a look at this little macro, maybe it gets you going:
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 (19-5-2011 01:02:40) deeplink to this comment
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 (19-5-2011 02:44:56) deeplink to this comment
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 (19-5-2011 03:28:41) deeplink to this comment
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 (19-5-2011 06:37:48) deeplink to this comment
Hi Sebastien,
Odd, as it works flawlessly for me. Does the slicer actually have any items visible?
Comment by: Sebastien (19-5-2011 06:48:17) deeplink to this comment
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 (19-5-2011 07:40:28) deeplink to this comment
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 (22-5-2011 21:52:40) deeplink to this comment
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:
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 (2-6-2011 19:48:12) deeplink to this comment
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 (5-6-2011 23:55:44) deeplink to this comment
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:
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 13:23:04) deeplink to this comment
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 (7-6-2011 00:15:29) deeplink to this comment
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 (6-7-2011 05:35:03) deeplink to this comment
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 (6-7-2011 06:49:44) deeplink to this comment
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!
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 (6-7-2011 06:50:33) deeplink to this comment
Hi Ian,
NB: The code goes into the ThisWorkbook module!
Comment by: Shadi Al. Kurdi (27-7-2011 23:11:36) deeplink to this comment
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 (22-8-2011 03:01:54) deeplink to this comment
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: Trisha (27-8-2011 09:23:46) deeplink to this comment
My slicer function is missing also. My spreadsheet is saved in .xlsx version, but still missing. Any suggestions?
Comment by: Jan Karel Pieterse (29-8-2011 01:32:27) deeplink to this comment
Hi Trisha,
If you add a new pivot table to the xlsx file, does that new Pivot table allow inserting of a slicer?
Comment by: RealParadox (29-10-2011 08:13:06) deeplink to this comment
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 (31-10-2011 01:07:57) deeplink to this comment
Hi RealParadox,
What exactly is the problem?
Comment by: Vivek Gokhale (3-11-2011 18:16:56) deeplink to this comment
Hi!!!
This article was great.
Is it possible to wrap text data in the slicer button???
Would really help.
Thanks
Comment by: Jan Karel Pieterse (4-11-2011 05:07:14) deeplink to this comment
Hi Vivek,
I'm afraid not!
Comment by: Melroy Beeks (6-11-2011 15:21:11) deeplink to this comment
In my Micro
soft does not have a SLICER button
Comment by: Jan Karel Pieterse (8-11-2011 00:53:15) deeplink to this comment
Hi Melroy,
You need to have Excel 2010 to be able to use Slicers in Pivot tables.
Comment by: s hartman (18-1-2012 13:56:22) deeplink to this comment
How can I use one slicer when I have multiple pivotcaches (and multiple pivot tables)?
Comment by: Jan Karel Pieterse (18-1-2012 23:38:18) deeplink to this comment
Hi Seana,
I'm afraid you cannot tie one (set of) slicer(s) to more than one Pivot cache. However, you can use a bit of VBA code to mimick that behaviour. Find some examples in the comments of the next page:
https://jkp-ads.com/Articles/slicers03.asp?AllComments=True
Comment by: Dipti (20-1-2012 09:21:40) deeplink to this comment
Can i put slicer on the same page as the Pivot table?. I am trying to make it look like how we select from the drop down and get the related information in pivot table..
Comment by: Jan Karel Pieterse (22-1-2012 22:24:18) deeplink to this comment
Hi Dipti,
Of course you can!
Comment by: Jason Bisulco (25-1-2012 06:13:50) deeplink to this comment
Can you group the data in a slicer? (e.g. in number incremeents say 1 - 20 or group monday - wednesday if a date.
Comment by: Jan Karel Pieterse (25-1-2012 07:38:59) deeplink to this comment
Hi Jason,
No, but you can group the appropriate field of the pivot table and add a slicer for the grouped field.
Comment by: Andre (27-1-2012 10:45:01) deeplink to this comment
Hi Jan Karel,
Is it possible to add value filters, i.e. values greater than 1,000,000 to the slicers?
If so, what about multiple value filters?
Cheers,
Andre
Comment by: Jan Karel Pieterse (27-1-2012 11:11:42) deeplink to this comment
Hi Andre,
I don't think ou can do that, no.
Comment by: Kajsa-Stina Ohlström (3-2-2012 07:40:48) deeplink to this comment
When i should connect my slicer to more then one of my pivot tables, there comes a message that it "the slicer can not currenty be created or connected to the pivot table". What's wrong ?
Comment by: David (4-2-2012 09:08:40) deeplink to this comment
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?
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: Kajsa-Stina (5-2-2012 23:46:01) deeplink to this comment
Hi
When I try to add more than one pivot table connections to my slicer, I got a message that "An error occured and the slicer can not currently be created or connected to pivot tables". What's wrong ?
Comment by: Jan Karel Pieterse (6-2-2012 04:57:24) deeplink to this comment
Hi Kajsa-Stina,
You can only add pivot tables form the same pivot table cache to one slicer, perhaps the pivot tables you are trying to combine are independently tied to their own source?
Comment by: Kajsa-Stina (13-2-2012 07:41:02) deeplink to this comment
Thank you for the answer.
All pivot tables i try to connect to my slicer are using an area that I've named "rawdata" as datasource. This area is just in another sheet in the same workbook.
When checking name manager, I can see that "rawdata" is right defined, however, my slicers are also listed in the name manager, but they have no reference...
/Kajsa-Stina
Comment by: Jan Karel Pieterse (13-2-2012 08:12:58) deeplink to this comment
Slicers do not have a reference in Name Manager, that is "by design". How many Pivot caches does your workbook have?
Alt+F11 to the VBA editor
control+g to the immediate window
type:
?ActiveWorkbook.PivotCaches.Count
press enter with your cursor on that line.
Comment by: Kajsa-Stina (14-2-2012 00:48:22) deeplink to this comment
I've just 1 Pivot Cache (getting that after doing as you told)
Comment by: Jan Karel Pieterse (14-2-2012 03:00:29) deeplink to this comment
Hi Kajsa-Stina,
In that case I truly have no idea why it does not work.
Perhaps you should try to recreate the pivot tables in a blank workbook just to check if it does work?
Comment by: Kajsa-Stina (14-2-2012 04:45:44) deeplink to this comment
I've solved my problem by "remaking" all pivot-tables by copy/paste for all tables that should be connected to the same slicer. Now it works.
Comment by: Jan Karel Pieterse (14-2-2012 07:03:37) deeplink to this comment
Hi Kajsa-Stina,
Excellent, glad you were able to solve the problem!
Comment by: Aaron (20-2-2012 14:01:12) deeplink to this comment
Once you've created a custom slicer template, is it possible to save it and access it on another workbook? I've created my own slicer template (color, font, shading, etc) that I want to use in different workbooks, but the "created" templates are only available within that one workbook.
Cheers,
Aaron
Comment by: Jan Karel Pieterse (20-2-2012 22:54:55) deeplink to this comment
Hi Aaron,
A quick way is to open the workbook with the custom slicer style applied to a slicer (not sure of that is necesary) and copy the slicer and paste it into the other workbook. The slicer style is copied with it.
Comment by: Aaron (22-2-2012 15:36:32) deeplink to this comment
Cheers! That is quite simple and works perfectly!
Comment by: Midimal (28-2-2012 04:02:22) deeplink to this comment
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 (5-3-2012 05:12:11) deeplink to this comment
Hi Midimal,
YOu could use this function to detect whether or not a slicers is in filter mode:
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: Irvin Jaffe (7-3-2012 12:32:47) deeplink to this comment
RE: Excel 2010. I have a slicer with 6 choices. I want to show only 4 of the 6 (2 of the 6. And, of the 4 that I want visible, I want to change the text. How do I do this?
Comment by: Jan Karel Pieterse (8-3-2012 02:39:30) deeplink to this comment
Hi Irvin,
I think you can only achieve that by removing data from the source so that those items are not on the slicer. Also, you cannot change what is shown on the slicer, other than by changing the source data.
Comment by: bill (29-3-2012 15:19:18) deeplink to this comment
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 (30-3-2012 01:54:14) deeplink to this comment
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. (3-4-2012 09:26:35) deeplink to this comment
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 (3-4-2012 21:54:29) deeplink to this comment
Hi Mark,
The number format of a slicer cannot be adjusted I'm afraid.
Comment by: Kathryn (3-5-2012 10:08:53) deeplink to this comment
How do I change the date format in a slicer button? My table data has the date as 6/1/2011, but I've formatted it to display Jun-11. However, the slicer button still displays 6/1/2011.
Comment by: Jan Karel Pieterse (3-5-2012 11:18:35) deeplink to this comment
Hi Kathryn,
I just tried this and it looks like the slicer adheres to the date format as set in the source data table.
Comment by: Rob (16-5-2012 06:20:02) deeplink to this comment
I have slicers on one tab and pivot tables on another.
When I copy these tabs to a new workbook, my slicers do not copy (or macro buttons for that matter). They used to copy fine when I went through this process, but I must have changed something along the way.
Is there a way to reset this function ?
Thanks,
Rob
Comment by: Jan Karel Pieterse (16-5-2012 07:53:58) deeplink to this comment
Hi Rob,
To be honest, I have no idea! Sounds like your file may be experiencing problems?
Are you copying all sheets that belong to each other in one go?
Comment by: Rob (17-5-2012 04:03:16) deeplink to this comment
Hi Jan,
I have tried the copy process using all the tabs and just single tabs. I have also tried it using the tab drag process from one open workbook to another and still lose my slicers and buttons.
May have create a different work around....
Comment by: Dan (17-5-2012 04:46:15) deeplink to this comment
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 (18-5-2012 07:21:55) deeplink to this comment
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 (18-5-2012 12:01:08) deeplink to this comment
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 (20-5-2012 23:18:34) deeplink to this comment
Hi Dan,
What happens if you remove the On Error Resume Next statement? It will mask any problems in the code.
Comment by: T Pearce (22-5-2012 14:30:49) deeplink to this comment
I am using slicers in a financial application I created and it works great filtering the data, but when you drill down to see the details, it does not filter the data at all. Can you give me some insight on how to prevent this from happening. I don't understand why it does not filter the detail data.
Comment by: Jan Karel Pieterse (23-5-2012 23:14:45) deeplink to this comment
Hi T.,
This is one of those situations where a sample file is needed to see what is going on. Please use the email address below to send me one (if possible).
Comment by: katia (25-5-2012 14:24:56) deeplink to this comment
Hi Eileen,
I am preparing for my next class. In my Pivot Table -> Options -> Sort, the "Insert Slicer" is not shown at all.
Can you help me?
Thank you so very much in advance.
Comment by: Jan Karel Pieterse (26-5-2012 02:56:53) deeplink to this comment
Hi Katia,
Eileen is not my name :-), it is the name of a forum where you can ask Excel questions.
To your question: Do you have Excel 2010? Slicers are not available in older Excel versions.
Comment by: Lizzie (7-6-2012 23:11:14) deeplink to this comment
I have created my slicer and formatted it to match my table but I need to display only months and not the full date so that I can sort my table by month. How do I modify my date so that it only shows the month and not the full date? Thanks
Comment by: Jan Karel Pieterse (8-6-2012 11:58:19) deeplink to this comment
Hi Lizzie,
I expect you can do this by first grouping your date field in the pivot table by months and then inserting a new slicer for the month.
Comment by: Hank Buonforte (23-6-2012 01:09:17) deeplink to this comment
Does anyone know how I can get a slicer search bar? I want to be able to type something into the search bar and press enter and have it select what I typed in the slicer.
Comment by: Jan Karel Pieterse (25-6-2012 07:08:25) deeplink to this comment
Hi Hank,
Unfortunately, no there isn't one. Would have been a great addition!
Comment by: Anwaar (2-7-2012 06:55:00) deeplink to this comment
Hi
When I tried to add more than one pivot table connections to my slicer, I get a message stating "An error occured and the slicer can not currently be created or connected to pivot tables". Why I am I unable to connect? I've just 1 Pivot Cache (checked as per your instructions above.
I am facing the same problem as Kajsa-Stina (2/5/2012 11:46:01 PM). I was curious to know if you have found a work around to it instead of re-creating all the tables.
Please advise.
Comment by: Jan Karel Pieterse (2-7-2012 11:11:26) deeplink to this comment
Hi Anwaar,
Perhaps Excel thinks that the pivot table was created in an older version of Excel (I have seen that happen to files which were created in Excel 2007). Try recreating the pivot table. Also: make sure the file is NOT saved in Excel 97-2003 file format.
Comment by: Steve (5-7-2012 14:25:19) deeplink to this comment
My fiscal year runs from July to June. After creating my pivot table and inserting a slicer for the months, the slicer automatically sorts it from January to December. I cannot find an option to customize the sort order of the slicer.
Thanks for any help you can give me.
Comment by: Jan Karel Pieterse (5-7-2012 19:28:37) deeplink to this comment
Hi Steve,
Perhaps you can add a custom sort order in the custom lists item in Excel Options?
Comment by: Steve (12-7-2012 16:59:57) deeplink to this comment
Having created a custom slicer style in a workbook, how do you export that style to be used in another workbook?.
Thanks
Comment by: 100tsky (16-7-2012 02:15:52) deeplink to this comment
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 (7-8-2012 11:35:45) deeplink to this comment
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: Jan Karel Pieterse (7-8-2012 11:37:48) deeplink to this comment
Hi STeve,
You can copy a slicer with that style to the new workbook and after that delete the slicer (I think).
Comment by: David (15-8-2012 13:22:42) deeplink to this comment
I am trying to use slice, but when I go to set it up the slice icon is dimmed in the tool bar. Am I missing something? How do i get the slice icon to enable?
David
Comment by: Jan Karel Pieterse (15-8-2012 18:29:15) deeplink to this comment
Hi David,
The pivot table you are trying to connect to a slicer must be in a file create by Excel 2010 and saved in the 2010 file format (xlsb, xlsx or xlsm). Pivot tables created in earlier versions or in files of older Excel versions cannot be combined with a slicer.
Comment by: Meggan (17-8-2012 00:22:42) deeplink to this comment
I have Excel 2010 with Win 7 -- I can neither modify nor delete custom styles (either Pivot Table or Slicer) and it's driving me CRAZY! The options are available for me to click, but nothing happens after I do. Seen it? Got a fix?
Thanks!
Comment by: Jan Karel Pieterse (17-8-2012 11:53:06) deeplink to this comment
Hi Meggan,
Odd indeed. Perhaps there are slicers/tables that have the custom style applied to them, making Excel refuse to remove the style?
Comment by: Chris (21-8-2012 00:08:12) deeplink to this comment
Okay, when I first saw slicers, I thought, yeah, they're nice in that they look better than pivot table report filters, but that's really all they are - nicer looking pivot table report filters.
Then I read this article and discovered that they will apply your hierarchical groupings to your filtering. Okay. That's cool. I think more of slicers now than I did two minutes ago (I'm a slow typer ;-))
Comment by: Carlos (8-9-2012 00:52:17) deeplink to this comment
I have a pivot table in which I use two fields as report filters. The first field is a set of item groups, and the second field is a set of items contained in an item group. The various item groups have differrent sets of items. I would like to set up two slicers (item group, and item) such that when I pick a value in the item group slicer, the item slicer only shows the values appropriate for the selected item group. Can this be done, and if so how. I appreciate your help. Thanks.
Comment by: Jan Karel Pieterse (10-9-2012 10:02:04) deeplink to this comment
Hi Carlos,
Slicers offer that option by default, but how they behave when items are not applicable is something you can control in the slicer options dialog, which is accessible through a right-click on the slicer and select "Slicer settings".
However, you cannot set it to not show non-applicable items, you can only make sure those are shown at the bottom of the list and in a different color.
Comment by: Jacob Jørgensen (12-9-2012 11:39:17) deeplink to this comment
Hi,
I have designed a pivottable, and I want to use the slicer tools. My idea is to use a togglebutton, so if it is activated it will show the slicer, and deactivated it will not appear. Is that possible?
Hope you understand.
Kind regards
Jacob
Comment by: Jan Karel Pieterse (13-9-2012 09:48:16) deeplink to this comment
Hi Jacob,
Sure that is possible, but it requires a bit of VBA which you tie to the toggle button (though I would probably a checkbox instead).
I added a checkbox from the forms toolbar to a worksheet. The checkbox's name is "Check Box 1". I assigned this macro to the checkbox:
Dim oSl As Slicer
Dim oSc As SlicerCache
For Each oSc In ThisWorkbook.SlicerCaches
For Each oSl In oSc.Slicers
If oSl.Parent.Name = ActiveSheet.Name Then
If ActiveSheet.CheckBoxes("Check box 1").Value = -4146 Then
oSl.Height = 0
Else
oSl.Height = 100
End If
End If
Next
Next
End Sub
Note that you cannot hide a slicer, only set its heigh or width to zero. This means if you want to show the slicer again, you must remember what its original height was. I did not include any logic in this macro to handle that though.
Comment by: Mark (25-9-2012 11:10:26) deeplink to this comment
I am trying to copy a slicer from one workbook to another. The slicer i pasted is no longer linked to the pivotable on the original file i copied from. How can i do it?
Comment by: Jan Karel Pieterse (25-9-2012 13:24:06) deeplink to this comment
Hi Mark,
Looks like a slicer cannot be tied to a pivotcache in a different workbook.
Comment by: Michelle (26-9-2012 15:53:51) deeplink to this comment
Hi,
One of my slicers contains a significantly long list of names. I realize that if I need to do a search I can do so in the pivot table itself. However, is there a way I can give the user the ability to do this in the slicer?
By the way, using Excel 2010.
Thanks in advance!
Comment by: Jan Karel Pieterse (26-9-2012 19:15:07) deeplink to this comment
Hi Michelle,
I know, that is a PITA. The only way is the one you wrote about.
Comment by: Paul Kelly (2-10-2012 11:47:39) deeplink to this comment
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 (2-10-2012 13:13:21) deeplink to this comment
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: Rebecca Haack (17-10-2012 20:20:01) deeplink to this comment
Can you get a total for the slicer's selection? For example, If I have the slicer set for January, can I get a total for all the amounts the slicer has selected for January?
Comment by: Jan Karel Pieterse (18-10-2012 11:03:02) deeplink to this comment
Hi Rebecca,
The pivot table shows the total for the slicer's selection, provided you have included totals in your pivot table of course.
Comment by: Ricardo Madaleno (18-10-2012 15:04:53) deeplink to this comment
Hi,
I have an issue. Whenever I select one of the filters in my slicer, it changes the height of my header rows...
Is there a way to fix this?
Thanks
Comment by: Jan Karel Pieterse (19-10-2012 12:00:22) deeplink to this comment
Hi Ricardo,
Perhaps you can fix this by changing the pivot table's settings regarding keeping formatting and adjusting column widths?
Comment by: Ricardo Madaleno (19-10-2012 13:48:20) deeplink to this comment
Hi Jan,
no, that's not it... i have my pivot with the option to autofit unchecked.
this is very strange... can't seem to fix it.
Comment by: Jan Karel Pieterse (19-10-2012 19:08:39) deeplink to this comment
Hi Ricardo,
If the slicer is filtering the page filter, perhaps the column where the page filter is located is too narrow for certain values If that cell has word wrap on, it will force Excel to increase row height to display the full value.
Comment by: Anne (30-10-2012 05:45:19) deeplink to this comment
Hi!
I have been using slicers as a great way to indicate what areas of our business that needs attention. I want to do this once a month. But when I try to update the pivot table/ refresh, nothing changes in the slicers. Help would be much appropriated :) Anne
Comment by: Jan Karel PIeterse (30-10-2012 08:57:14) deeplink to this comment
Hi Anne,
There should be an option in the slicer settings that allows you to indicate whether or not to have the slicer include items which are no longer in the pivotcache. Is that what you are after?
Comment by: Anne (30-10-2012 11:04:05) deeplink to this comment
Thank you for your reply.
This is the message I get when I try to update my with the slicers; "The data source of a PivotTable connected to the slicers that are also connected to the Pivot Tabel cannot be changed. To change the data source first disconnet the slizers from this and other pivottables".
So my question to you is, is there any way to get around this? I would really like to keep my slizers and not have to do them wach time i update my pivottable.
Thanks again!
Comment by: Jan Karel PIeterse (30-10-2012 12:06:14) deeplink to this comment
Hi Anne,
Odd, I just created a PT bound to an Access database, with one slicer connected to it. If I change the PT source (by editing the connection properties), it just lets me do that without complaining.
Comment by: Oz (13-11-2012 11:05:40) deeplink to this comment
Hi Jan,
Like Ricardo I'm having problems with my slicers resizing my pivot table, though mine are changing the column width:
-My table is set to not resize on update.
-The values in my table are never more than 2 digits.
-The row labels are abbreviated month names.
-It doesn't matter where on the sheet the slicer is.
-It doesn't help if I stretch the columns out to allow some extra room.
-Whenever I change the months the row label column resizes.
I think it may be seeing the full name of the month even though it shows the abbreviated name, but if I stretch it to allow room for this it'll shrink for shorter month names.
Thanks for your help
Comment by: Jan Karel Pieterse (13-11-2012 11:35:51) deeplink to this comment
Hi Oz,
Can you perhaps email that workbook to me (pls refer to this page when doing so so I won't discard that email as spam)?
Comment by: Ram (13-11-2012 16:15:34) deeplink to this comment
Hi Jan,
Awesome job... I read through every post. Its really a task to keep replying.. But We all truly appreciate.
I read through all the post but may have missed this.
I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this.
Thanks again.
Comment by: Oz (13-11-2012 16:26:01) deeplink to this comment
Hi Jan,
Thanks for getting back to me so quickly.
I'm afraid I can't send it to you as it contains sensitive company data. I tried to remove all the personal data and give you a version with just the sheets in question, and what do you know; the problem went away!
Still there on the original though :(
Comment by: Oz (13-11-2012 16:29:43) deeplink to this comment
Well that's embarrasing;
I had a feeling the gentleman who created this workbook might be behind the fault so I looked through the page and found, right at the bottom, another pivot table, with autofit on!
Well, at least its solved, and I've got another horror story to share with the wife :)
Thanks again Jan
Comment by: Rebecca (13-11-2012 16:34:12) deeplink to this comment
Hi Jan,
Did you find a solution to the problem posted by T Pierce on 5/22/12? I am having the same problem. I want to have slicers in a worksheet that are linked to multiple pivot tables and charts, and be able to drill into the details for just the results filtered by the slicers. Thanks!
Comment by: Jan Karel Pieterse (13-11-2012 19:07:52) deeplink to this comment
@Oz: Thanks for letting me know, another mystery solved.
@Rebecca: No, but perhaps you can ask your question over at Eileen's lounge?
@Ram: I think the only way is to add a bit of VBA code that resets the chart after filtering.
Comment by: Trish McDermott (7-12-2012 00:11:04) deeplink to this comment
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 (7-12-2012 13:04:56) deeplink to this comment
Hi Trish,
Like so perhaps?
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: Mark Fay (7-12-2012 17:55:29) deeplink to this comment
one big problem I can't get past with slicers is that that deleting or modifying a custom slicer does not work. The only way I have of modifying a customer slicer is to duplicate it, the modify box then pops up. I now have 11 custom slicers in various stages and cannot delete any. Is there some trick to get modify and delete to work.
The prompt dialogue boxes come up but don't actually do anything.
Comment by: Bill (8-12-2012 23:42:53) deeplink to this comment
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 (9-12-2012 00:25:41) deeplink to this comment
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 (10-12-2012 08:29:16) deeplink to this comment
Hi Bill,
Actually, it is the pivot table that activates the event routine, not the slicer. Anyway, perhaps this gets you going:
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: Jan Karel Pieterse (10-12-2012 08:33:16) deeplink to this comment
I expect you are referring to a custom slicer style?
Some important things to know:
1. You can only modify a custom slicer style. Built-in styles cannot be modified.
2. (and I think this is the one you need!) After duplicating a built-in style and modifying it, you have to apply the new custom slicer style to the slicer in question. Only then do your modifications appear.
Comment by: Mark Fay (10-12-2012 08:56:05) deeplink to this comment
thanks Jan, yes it is a custom style. I can modify after duplicating but then cannot modify it again unless I duplicate again. Also cannot delete it. I want to modify a style check it then make more modifications until I have what I want.
Any ideas?
Comment by: Jan Karel Pieterse (10-12-2012 09:00:13) deeplink to this comment
Hi Mark,
I can modify them as much as I like after creating a custom one. I can also delete them, even though they are in use on a slicer.
Perhaps your worksheet/workbook is protected?
Comment by: Obwan (11-1-2013 05:54:19) deeplink to this comment
Hi
I saved my Excel 2010 workbook with 10 pivot tables connected to 1 slicer. Works perfectly. Now I have added additional pivot tables but cannot get them to list in the slicer connections. Is there a limit to the number of connections or is there a trick I am missing. Thank you in advance.
Comment by: Jan Karel Pieterse (11-1-2013 16:30:48) deeplink to this comment
Hi Obwan,
The trick is that the pivot tables must all share the same pivot cache.
Comment by: Amir (17-1-2013 18:01:31) deeplink to this comment
Hi
I am using pivot tables and slicers to create dashboard reports. The info you have provided on this web page is fantastic and very well put together.
I have a large pivot table with fields such as cost centres, cost, date and product code. I have created slicers for cost centres and product code.
The individual cost centres are grouped into North Division and South Division.
My question is can I create slicers for individual cost centres, all North Division cost centres, all South Division cost centres and finally whole Group cost centres? I hope this makes sense! Would very much appreciate your thoughts or advice.
Comment by: Jan Karel Pieterse (18-1-2013 16:33:52) deeplink to this comment
Hi Amir,
No, all you can do is put a slicer up there that filters on Division. The cost center slicer will then (if properly setup) show the relevant cost centers at the top and the other ones greyed out near the bottom.
Comment by: Bob (24-1-2013 09:40:12) deeplink to this comment
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 (24-1-2013 10:19:26) deeplink to this comment
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 (24-1-2013 18:51:55) deeplink to this comment
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: Narayan (30-1-2013 07:32:51) deeplink to this comment
Can I connect two slicers (having the same dataset)?
My requirement is to change the data in slicer2 depending on the input from Slicer1. Accordingly the data in the pivot tables change.
Comment by: Jan Karel Pieterse (30-1-2013 12:51:52) deeplink to this comment
Hi Narayan,
I'm not sure what you mean by "connecting" them, the slicers are connected and do reflect filters of other slicers (of you have set the properties accordingly).
Comment by: Tiana (7-2-2013 05:34:00) deeplink to this comment
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 (7-2-2013 20:33:57) deeplink to this comment
Hi Tiane,
You could do it like this:
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: Stephen (9-2-2013 00:06:03) deeplink to this comment
Is there any way to save your custom slicer styles so they are available across workbooks?
Comment by: Jan Karel Pieterse (10-2-2013 20:34:21) deeplink to this comment
Hi Stephen,
If you copy a slicer with that slicer style applied to a fresh workbook and then save-as that workbook as book.xltx (or m) to your xlstart folder the slicerstyle should become available to each new workbook you start with control+n.
Comment by: Beth (13-2-2013 01:53:52) deeplink to this comment
I am sending out a pivot table to several folks and I'm using my first slicer. Lovely tool. However, I typically protect the sheet to protect those folks from themselves. :)
You know, accidentally changing up the columns, etc. When I do that, the slicer stops allowing the user to select a different item, even tho the users of the pivot table can still change the sort or perform additional filtering within the pivot table itself. Is the slicer not considered part of the pivot table when I select my protection options?
Comment by: Jan Karel Pieterse (13-2-2013 08:54:14) deeplink to this comment
Hi Beth,
This can be done, but it is tricky:
Go to the “size and properties” dialog and uncheck the “locked” property for the slicer, THEN protect the sheet with “use PivotTables” (if you’re using pivot slicer) or “use autofilter” (if you’re using table slicer in Excel 2013).
Comment by: Arjo (15-2-2013 10:19:30) deeplink to this comment
Hi,
I use slicers a lot. I used to be able to copy them around my excel file.
Today I was able to create a slicer at the pivottable sheet, but I could not copy paste it to my pivotgraph sheet. It was pasted as an image.
I also do not have the opportunity to add a slicer when I have the graph selected. Something which normally is possible.
Is there something I can do to fix this?
I need the slicers a lot for creating a dashboard but I have to be able to move the slicers around my excel file.
thanks!
Comment by: Jan Karel Pieterse (15-2-2013 15:53:10) deeplink to this comment
Hi Arjo,
I haven't heard of that problem before. Is the chart a chart embedded on a sheet or on a chartsheet? Perhaps chartsheets cannot hold slicers?
Perhaps the file/sheet is protected?
Comment by: Arjo (18-2-2013 11:03:13) deeplink to this comment
I am not 100% sure, but I believe that chart sheets cannot hold slicers.
I placed the graph on a regular sheet and everything worked fine.
Although it would be nice to use slicers with multiple data sheets, I know this is not going to happen any soon. So for this moment I will have to create multiple slicers.
Comment by: Jan Karel Pieterse (18-2-2013 11:54:28) deeplink to this comment
Hi Arjo,
What do you mean by using slicers with multiple data sheets?
Comment by: Arjo (18-2-2013 13:28:22) deeplink to this comment
I'd like to connect 1 slicer to pivottables from different data sources. something which is not possible...
for example: I have 2 data sources. data sheet 1 and data sheet 2. In both the field 'account' is available and in both the values are the same.
I want to have 1 slicer to with which I can filter the account value in all the connected pivottables.
Comment by: Jan Karel Pieterse (18-2-2013 17:07:33) deeplink to this comment
Hi Arjo,
Check out this page, in the comments there should be some code to tie two slicers together.
https://jkp-ads.com/Articles/slicers03.asp
Comment by: Debbie Leonard (21-2-2013 04:01:32) deeplink to this comment
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 (21-2-2013 09:55:29) deeplink to this comment
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 (26-2-2013 01:17:18) deeplink to this comment
How do I set the filter to the first item in each of my slicers?
Thanks,
Debbie
Comment by: Jan Karel Pieterse (26-2-2013 08:52:37) deeplink to this comment
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)
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 (12-3-2013 06:22:15) deeplink to this comment
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:
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)