Connecting a single Slicer to multiple PivotTables

[marcus729]

I am using Excel 2010 for the following:

I have ten Pivot tables on a "Dashboard" sheet. I want to create a slicer that filters all ten pivot tables. When I try to connect the pivot tables to the slicer the first one works but the second one fails with the 1004 error.

I checked to see if all ten pivot tables use the same pivot cache and they do not.

I tried setting all to use the same pivot cache but that fails with the 1004 error also.

This is the code I use to create the Slicer and connect pivot tables to it:

    ActiveWorkbook.SlicerCaches.Add(Sheets("DashBoard").PivotTables("pta"), _
        "field 1").Slicers.Add Sheets("DashBoard"), , "field 1", _
        "Select field 1                                                           " & _
        "HOLD DOWN CTRL KEY TO SELECT MULTIPLE values. CLICK ON FILTER ICON TO CLEAR.", 51.12 _
        , 1.44, 768, 56
    ActiveWorkbook.SlicerCaches("Slicer_field_1").Slicers("field 1"). _
        NumberOfColumns = 10
    ActiveWorkbook.SlicerCaches("Slicer_field_1").Slicers("field 1").Style _
        = "SlicerStyleDark1"
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptb"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptc"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptd"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("pte"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptf"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptg"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("pth"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptj"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptk"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptl"))
    ActiveWorkbook.SlicerCaches("Slicer_field_1").PivotTables.AddPivotTable (Sheets("DashBoard") _
        .PivotTables("ptm"))

I use this code to make each pivot table use the same pivotcache:

Private Sub ChangePivotCache()
'pivot table tutorial by contextures.com
'change pivot cache for all Pivot Tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet
Dim i As Long

i = Sheets("Dashboard").PivotTables("pta").CacheIndex

    For Each pt In Sheets("DashBoard").PivotTables
    If pt.name = "pta" Then
    Else
        pt.CacheIndex = i
    End If
    
    Next pt

End Sub

I need help to determine why the 1004 error is being thrown and what I need to do to use a single Slicer to filer all ten pivot tables?

Thanks in advance for your time and help,
Marcus

 

[Domenic]

I tried your code and it seems to work fine. Make sure that the name of the field is spelled correctly. Also, note that the spelling is case-sensitive. In any case, assuming that each pivot table uses the same cache, here's another way...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] CreateSlicerAndConnectAllPivotTables()
    
    [COLOR=darkblue]Dim[/COLOR] oSCache [COLOR=darkblue]As[/COLOR] SlicerCache
    [COLOR=darkblue]Dim[/COLOR] oSlicer [COLOR=darkblue]As[/COLOR] Slicer
    [COLOR=darkblue]Dim[/COLOR] oPT [COLOR=darkblue]As[/COLOR] PivotTable
    
    [COLOR=darkblue]Set[/COLOR] oSCache = ActiveWorkbook.SlicerCaches.Add( _
        Source:=Sheets("DashBoard").PivotTables("pta"), _
        SourceField:="field 1")
        
    [COLOR=darkblue]Set[/COLOR] oSlicer = oSCache.Slicers.Add( _
        SlicerDestination:=Sheets("DashBoard"), _
        Name:="field 1", _
        Caption:="Select field 1" & Space(59) & "HOLD DOWN CTRL KEY TO SELECT MULTIPLE values. CLICK ON FILTER ICON TO CLEAR.", _
        Top:=51.12, _
        Left:=1.44, _
        Width:=768, _
        Height:=56)
        
    [COLOR=darkblue]With[/COLOR] oSlicer
        .NumberOfColumns = 10
        .Style = "SlicerStyleDark1"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oPT [COLOR=darkblue]In[/COLOR] Sheets("DashBoard").PivotTables
        [COLOR=darkblue]If[/COLOR] oPT.Name <> "pta" [COLOR=darkblue]Then[/COLOR]
            oSCache.PivotTables.AddPivotTable oPT
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oPT
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!