Cannot Connect Slicer to Multiple Pivot Tables - E

问题:

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

回答1:

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.

回答2:

Problem Solved
Code steps:
1. Create Pivot Tables from one Pivot Cache
2. For each Pivot Table, create a Slicer Cache
3. For each Slicer Cache, create a Slicer
4. For each Slicer Cache, loop through Pivot Tables and add them to the SC

Code:

' Procedure

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

    varSlicers = wksDefPivots.Range("A2:B" & CStr(FindLastRow(wksDefPivots))).Value2
    For i = LBound(varSlicers, 1) To UBound(varSlicers, 1)
        Set objPT = Worksheets(sPivots).PivotTables(CStr(varSlicers(i, 1)))
        Set objPF = Worksheets(sPivots).PivotTables(CStr(varSlicers(i, 1))).PivotFields(CStr(varSlicers(i, 2)))
        Set objSlicerCache = wkbDash.SlicerCaches.Add(objPT, objPF.Name) 'Create SlicerCache for each Pivot Table
    Next i

    For Each objSlicerCache In wkbDash.SlicerCaches
        For Each objPT In objSlicerCache.PivotTables
            objSlicerCache.Slicers.Add wksSlicers, , objPT.Name, objPT.Name, 1, 1, 50, 100 'Add Slicers for each Pivot Table
        Next objPT
    Next objSlicerCache

    Set objSlicerCache = Nothing
    Set objPT = Nothing

    Call FormatSlicers(sSlicers) 'Format size & location of slicers

   For Each objSlicerCache In wkbDash.SlicerCaches
        For Each objPT In wksPivots.PivotTables
                objSlicerCache.PivotTables.AddPivotTable objPT 'Add every Pivot Table to every SlicerCache
        Next objPT
    Next objSlicerCache