Updating Multiple Slicers Selection from Common Source

[JD_GFI]

Hi Folks,

Been scratching my head about this one for a while. I've created 2 sets of pivot tables, both being driven by the same raw data, one set grouped by year&month, the other un-grouped. I have had to set 2 named ranges to achieve this as using one range only for all tables resulted in all pivot tables being grouped or ungrouped together.

My problem is that I now have 2 separate slicers, with identical values, to manipulate the same data. Is there any way to have one slicer automatically duplicate the selection of the other?

Many thanks,

James

 

[JD_GFI]

Hi folks,

Does anyone have any ideas on whether this can or can't be done?

 

[JD_GFI]

Ok,

Been thinking about this still and I'm guessing I need a couple of macros maybe.

1st Macro:

Loop through named slicers (currently 3 of them) available selections and set them all to unselected when the worksheet is accessed (forcing user to make an initial selection).

2nd Macro:

Detects when a selection is made or changed on the 1st slicer and store that selected option as a variable. It will then loop through the 2nd slicers available selections and set the selection matching the variable passed from the first to active. (slicers one and two are identical lists, just built from different pivot caches as one is grouped and the other is not)

The 3rd thing I need to happen is that the 3rd slicer is checked to see whether the variable exists, if so then enable that selection, if not then a default selection should be enabled. (3rd slicer does not contain all the available items in slicer 1 and 2 but has a default selection for behaviour when no matching criteria is available).

Background:

Slicer one reads from the same data source as slicer two but is used to pull data for daily stats. It is a list of all companies found in that data.

Slicer two reads from the same data source as slicer one but is used to pull data for monthly stats and therefore has to have its own pivotcache to allow grouping without affecting the tables used by slicer 1. It is an identical list of all companies found in that data to slicer 1's list.

Slicer three reads from its own table of targets for particular companies that are pre-defined, a default set of targets is included for "all other companies". Only certain companies have targets and therefore this list is not identical to slicers one and two.


Does that help make the problem a little clearer for anyone to have a stab at? My VBA skills are pretty limited which I freely admit

 

[RoryA]

1. To the best of my knowledge you cannot unselect all slicer items.
2. There is no event that gives you this info. The closest I can think of is the Worksheet's PivotTableUpdate event but that does not tell you why the pivot changed. You would have to loop through everything - something like:

Sub SynchSlicers()
    Dim PT1 As PivotTable, PT2 As PivotTable
    Dim slc1 As Slicer, slc2 As Slicer
    Dim slcCache1 As SlicerCache, slcCache2 As SlicerCache
    Dim slcItem1 As SlicerItem, slcItem2 As SlicerItem
    Set PT1 = ActiveSheet.PivotTables(1)
    Set slc1 = PT1.Slicers(1)
    Set slcCache1 = slc1.SlicerCache
    Set PT2 = ActiveSheet.PivotTables(2)
    Set slc2 = PT2.Slicers(1)
    Set slcCache2 = slc2.SlicerCache
    slcCache2.ClearManualFilter
    For Each slcItem1 In slcCache1.SlicerItems
        slcCache2.VisibleSlicerItems(slcItem1.Name).Selected = slcItem1.Selected
    Next slcItem1
End Sub

3. You could simply set it inside an error handler and then check if err.number = 0. If not, then the item didn't exist so do something else.

 

[JD_GFI]

Thanks rorya, will give this a try

 

[RoryA]

Re 1, you could simply clear all filters:

Sub ClearSlicerFilters()
    Dim slc As SlicerCache
    For Each slc In ActiveWorkbook.SlicerCaches
        slc.ClearManualFilter
    Next slc
End Sub

 

[JD_GFI]

Great Rorya,

Is there any way to specify the slicers to clear? I have multiple slicers throughout the workbook but would only want to target the 3 specifically.

 

[RoryA]

You can identify them by name too.