vba 使一张纸上的所有数据透视表在行扩展和折叠方面相互模仿

Making all pivot tables on one sheet mimic each other in terms of rows expanding and collapsing

提问by dootcher

Alright, I'm new to VBA but I know this has to be possible. I spent a while coding android applications, but I wouldn't call myself nearly an expert, probably not even intermediate to be honest. However, alas, excel doesn't use java. Here's my problem:

好吧，我是 VBA 新手，但我知道这必须是可能的。我花了一段时间编写 android 应用程序，但我不会称自己几乎是专家，老实说，甚至可能不是中级。但是，唉，excel 不使用 java。这是我的问题：

All I need is to make 6 other pivot tables on the same sheet mimic what I will call the primary pivot table. The only feature it needs to mimic though (for now I suppose) is when the primary expands/collapses, the others should follow suit.

我所需要的只是在同一张纸上制作 6 个其他数据透视表，模仿我将称之为主数据透视表的内容。它需要模仿的唯一功能（现在我想）是当主要扩展/折叠时，其他功能应该效仿。

I'm guessing that the code will be similar to an onclicklistener in java and when the code "hears" a click for a collapse or expansion in the primary pivot table, it just applies that same collapse or expansion to the other six. The other 6 pivots will always have the same row labels, so an error in carrying over the "location" of the click from the primary to the others should never be a problem.

我猜测该代码将类似于 Java 中的 onclicklistener，当代码“听到”主数据透视表中折叠或展开的点击时，它只会将相同的折叠或展开应用于其他六个。其他 6 个枢轴将始终具有相同的行标签，因此将点击的“位置”从主要点转移到其他点时出现错误应该永远不会成为问题。

I tried recording the expansion of one of the row labels in my pivot table and got this code back.

我尝试在数据透视表中记录行标签之一的扩展并取回此代码。

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").PivotItems("2005"). _
    ShowDetail = True

I know though, that this is the coding for executing that expansion (and ShowDetail = False would make it collapse). I think what I'm needing is like I said, a listener to "hear" the click of any expansion/collapse of the primary pivot table, a way to store/carry over information on what row label was clicked (the "location" of the click if you will), and then a generic version of the above code to execute on the other 6 using I'm guessing a for loop of sorts.

我知道，这是执行该扩展的编码（并且 ShowDetail = False 会使其崩溃）。我认为我需要的就像我说的那样，一个听众可以“听到”主数据透视表的任何展开/折叠的点击，一种存储/携带有关点击的行标签（“位置”）的信息的方法如果您愿意，请单击单击），然后使用上述代码的通用版本在其他 6 个代码上执行，我猜是某种 for 循环。

Am I on the right track slash any help guys? Thanks a ton as always.

我在正确的轨道上削减任何帮助吗？一如既往地感谢。

回答by Stepan1010

This should work for you:

这应该适合你：

Put this in a standard module(this is the less efficient method - because it checks all fields):

将其放在标准模块中（这是效率较低的方法 - 因为它会检查所有字段）：

Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(1)

Dim PivotTableIndex As Integer
Dim PivotFieldIndex As Integer
Dim PivotItemIndex As Integer

Dim PivotFieldIndexName As String
Dim PivotItemIndexName As String

Dim BoolValue As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

    For PivotFieldIndex = 1 To pt.PivotFields.Count

    PivotFieldIndexName = pt.PivotFields(PivotFieldIndex).Name

        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        PivotItemIndexName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name
        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndexName).PivotItems(PivotItemIndexName).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndexName).PivotItems(PivotItemIndexName).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

    Next PivotFieldIndex


Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Then to automatically run this macro on any PivotTable edit you need to put this in your Sheet1 code(let me know if you need help doing that).

然后要在任何数据透视表编辑上自动运行这个宏，你需要把它放在你的 Sheet1 代码中（如果你需要帮助，请告诉我）。

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

End Sub

It will work if all your pivot tables are on sheet one. You might have to copy/paste into wordpad or another text editor first because I didn't worry about line length limits(watch out for word wrap).

如果所有数据透视表都在第一张纸上，它将起作用。您可能必须先复制/粘贴到写字板或其他文本编辑器中，因为我不担心行长限制（注意自动换行）。

EDIT/ADDITION:

编辑/添加：

This is how you put code on a specific sheet object:

这是将代码放在特定工作表对象上的方式：

EDIT2/ADDITION2 - EFFICIENCY METHOD:

EDIT2/ADDITION2 - 效率方法：

This method will dramatically increase the efficiency but you will have to tell it specifically which Field you want to be synced up(it won't sync them all):

此方法将显着提高效率，但您必须具体告诉它要同步哪个字段（它不会同步所有字段）：

Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)  'takes as argument - pt As PivotTable

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(1)

Dim PivotTableIndex As Integer
Dim PivotItemIndex As Integer
Dim PivotFieldIndex As String
Dim BoolValue As Boolean
Dim ItemName As String

Application.ScreenUpdating = False
Application.EnableEvents = False

PivotFieldIndex = "Year"

On Error Resume Next


        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail
        ItemName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

You will have to manually tell it in this line what field you want synced up:

您必须在此行中手动告诉它要同步的字段：

PivotFieldIndex = "Year"

I've found several other solutions online that use the same looping method to sync up pivot tables - the problem is that they all run into the same efficency problems when you get decent sized pivot tables. These somewhat get around that issue by including an IF statement that checks the Item.ShowDetail value before it sets it(because it takes alot longer to set the value than it does to just check it). Good Luck.

我在网上找到了几个其他解决方案，它们使用相同的循环方法来同步数据透视表 - 问题是当您获得合适大小的数据透视表时，它们都会遇到相同的效率问题。这些通过包含一个 IF 语句来解决这个问题，该语句在设置 Item.ShowDetail 值之前检查它（因为设置值比检查它花费的时间要长得多）。祝你好运。