使所有的数据透视表在一个表上相互模仿，以行扩展和折叠。

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，当代码“听到”主pivot表中的折叠或扩展时，它就会对其他六个应用相同的折叠或扩展。其他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.

我是在正确的轨道上吗?一如既往的感谢。

1 个解决方案

#1

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).

如果所有的数据透视表都在表1上，那么它就会工作。您可能需要先复制/粘贴到wordpad或另一个文本编辑器中，因为我不担心行长度限制(注意单词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语句来检查该项目，从而绕过这个问题。ShowDetail值在设置它之前(因为设置这个值比检查它要花费更多的时间)。祝你好运。