无法将切片器连接到多个数据透视表-错误#424“需要对象"

问题描述

我正在尝试使用此处描述的方法将切片器连接到多个数据透视表: http://dailydoseofexcel.com/archives/2014/08/05/slicers-and-slicercaches/

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

问题:我在此行上收到错误#424"Object Required":

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

基于Bob的评论，我修改了循环以尝试将数据透视表挂接到SlicerCache对象而不是Slicer.我仍然收到错误#424需要对象"

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"

修订后的循环:

    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

主要更新:我已经修改了代码，并且非常接近.新代码序列:
1.创建1个PivotCache
2.使用PivotCache创建许多数据透视表
3.从第一个数据透视表创建1个SlicerCache
4.将其他数据透视表添加到SlicerCache
5.循环遍历SlicerCache中的每个数据透视表，然后创建切片器.

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.

代码:

   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

过滤器的屏幕截图

推荐答案

问题已解决
代码步骤:
1.从一个数据透视缓存创建数据透视表
2.为每个数据透视表创建一个Slicer缓存
3.对于每个切片器缓存，创建一个切片器
4.对于每个切片器缓存，遍历数据透视表并将其添加到SC

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

代码:

' 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

这篇关于无法将切片器连接到多个数据透视表-错误#424“需要对象"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！