Collapsing and expanding of columns

We work on Pivot Tables where we collapse and expand columns. You might come across with situation where you have two pivot tables. What you want to do is when you expand one same column in other pivot table should also expand.

Below code can be used to do that. Put the following code in a module:


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(2) 'Mention the sheet name where Pivots are stored

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 = "VP" 'Mention column name which you want to collapse/expand

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

Put the below code in the active sheet where the pivots are placed:


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Executive_Manager(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Regional_Manager(Target)

End Sub