Cannot Connect Slicer to Multiple Pivot Tables - Error #424 "Object Required"

Question

I am trying to connect a Slicer to multiple Pivot Tables using the method described here: http://dailydoseofexcel.com/archives/2014/08/05/slicers-and-slicercaches/

First, I loop through my Pivot Tables and create a slicer for each:

wkbDash.SlicerCaches.Add(wksPivots.PivotTables(sPTName), sSlicerName). _
    Slicers.Add wksSlicers, , sSlicerName, sSlicerName, 1, 1, 50, 100

Then I loop through my Slicers and try to hook them to each Pivot Table

Problem: I am getting an Error #424 "Object Required" on this line:

wkbDash.SlicerCaches(objSlicer).PivotTables.AddPivotTable 
(wksPivots.PivotTables(varPTNames(i, 1)))

Code to hook Slicers to Pivot Tables:

' Declarations
Dim objSlicerCache  As SlicerCache
Dim objSlicer       As Slicer
Dim objPT           As PivotTable
Dim varPTNames      As Variant
Dim wksDefPivots    As Worksheet
Dim wkbDash         As Workbook
Dim i               As Integer

' Initialize Variables
Set wkbDash = Workbooks(sDash)
Set wksDefPivots = Workbooks(sDash).Worksheets(sDefPivots)
varPTNames = wksDefPivots.Range("A2:A" & FindLastRow(wksDefPivots)).Value2
i = 0

' Procedure
    For Each objSlicerCache In Workbooks(sDash).SlicerCaches
        For Each objSlicer In objSlicerCache.Slicers
            For i = LBound(varPTNames) To UBound(varPTNames)
               wkbDash.SlicerCaches(objSlicer).PivotTables.AddPivotTable (wksPivots.PivotTables(varPTNames(i, 1)))
            Next i
        Next objSlicer
    Next objSlicerCache

Based on Bob's comments I have modified the loop to try and hook the Pivot Tables to the SlicerCache object instead of the Slicer. I am still getting the Error #424 "Object Required"

Revised Loop:

    For Each objSlicerCache In Workbooks(sDash).SlicerCaches
        For j = LBound(varPTNames) To UBound(varPTNames)
           objSlicerCache.PivotTables.AddPivotTable (wksPivots.PivotTables(varPTNames(j, 1)))
        Next j
    Next objSlicerCache

Major Update: I Have revised the code and am very close. New code sequence:
1. Create 1 PivotCache
2. Use PivotCache to create many PivotTables
3. Create 1 SlicerCache from first PivotTable
4. Add additional PivotTables to SlicerCache
5. Loop through each PivotTable in SlicerCache and create a slicer.

Good news: All this code works. It creates many slicers, and each slicer will filter all the Pivot Tables

The Problem: All the Slicers are only filtering One Field (See Image) rather than each slicer providingng a different field to filter.

Code:

   For Each objSlicerCache In wkbDash.SlicerCaches
        objSlicerCache.Delete
    Next objSlicerCache

    sRF = Worksheets(sDefPivots).Range("B2").Value
    sPT = Worksheets(sDefPivots).Range("A2").Value

    Set objPT = Worksheets(sPivots).PivotTables(sPT)
    Set objPF = Worksheets(sPivots).PivotTables(sPT).PivotFields(sRF)

    Set objSlicerCache = wkbDash.SlicerCaches.Add(objPT, objPF.Name)

    Set objPT = Nothing
    Set objPF = Nothing

    For Each objPT In Worksheets(sPivots).PivotTables
        objSlicerCache.PivotTables.AddPivotTable objPT
    Next objPT

    For Each objSlicerCache In wkbDash.SlicerCaches
        For Each objPT In objSlicerCache.PivotTables
            objSlicerCache.Slicers.Add wksSlicers, , objPT.Name, objPT.Name, 1, 1, 50, 100
        Next objPT
    Next objSlicerCache

Screenshot of Filters

I went back to the code that creates the Pivot Tables and noticed that it creates a PivotTableCache for each Pivot Table, could that have anything to do with it? — Chris
It could. I'd suggest that you have a look at all of the different objects in your immediate window (i.e. debug window), and see which one isn't coming up as an object and so is throwing the error. For more on how to do that, see this. By the way, part of what I don't understand is that it appears that you're already adding the pivot tables by "creating a slicer for each" before you get into all this, just the other way around. What are you doing different here? — BobRodes
You are correct, I am now re-thining this. Before I would get a list of PTs then create a PivotCache & PT for each one. Then create a slicer for each PT. Then try and link each Slicer to every other PT. But now I am going to try and create One PivotCache and use it to create each PT. Then create One SlicerCache and use it for each Slicer. If all the PTs have the same PTCache and i use a PT to create a SlicerCache, and create every Slicer using that SC then I HOPE they will all connect. This is my first time programming with PT, Caches, and Slicers so i have no idea what I am doing. — Chris
The doc's a bit flimsy on what matches up with what, too. I'd say debug will be your friend there, so you can evaluate individual values in individual items and compare them. — BobRodes
I agree. Right now I am just throwing everything on the wall to see what sticks. I'll update once more to show where I am now. — Chris

BobRodes BobRodes · Accepted Answer · 2018-09-12T22:08:37

Looking at this doc it's evident that you have a syntax problem. To get an individual Slicer object:

wkbDash.SlicerCaches("someString")

This is consistent with general Collection syntax:

myCollection("aKeyValue")

Or:

myCollection(anOffsetInteger)

You are putting an object reference as the key value, rather than a string or integer.

Now, PivotTables is a collection associated with a single SlicerCache object. Your outer For Each is iterating through these, so if you're going to add a pivot table in each iteration, you need to do it to your iterated object, so:

For Each objSlicerCache In Workbooks(sDash).SlicerCaches
    For i = LBound(varPTNames) To UBound(varPTNames)
           objSlicerCache.PivotTables.AddPivotTable (wksPivots.PivotTables(varPTNames(i, 1)))
    Next i
Next objSlicerCache

You can't iterate the Slicers collection in each SlicerCache object inside the loop, and add the pivot table to the individual Slicer objects, because the object model isn't set up to associate a Slicer object directly with a PivotTable object. If you look in the doc, you'll see that the SlicerCache object has a PivotTables property, while the Slicers and Slicer objects do not.

Cannot Connect Slicer to Multiple Pivot Tables - Error #424 "Object Required"

2 Answers