I have a data source to create 3 pivot tables on the same sheet, TCSum.
The 3 tables are lined up right next to each other and all 3 have the same two Rows in the PivotTable Fields, in this order: Region - Market.
I don't want the Region column to be shown 3 times so I've hidden columns G and K on my TCSum sheet.
I'd like when I click the expand button in Column A for my first Region, it expands all 3 tables.
I found Making all pivot tables on one sheet mimic each other in terms of rows expanding and collapsing. I tried both methods there, and in the more efficient method did update this line with 4 different entries.
I tried:
PivotFieldIndex = "Region"
PivotFieldIndex = "[Region]"
PivotFieldIndex = "Market"
PivotFieldIndex = "[Market]"
I tried Region and [Region] because I thought since this is added to a data model the correct PivotFieldIndex may need to include them. I came to that conclusion by recording a macro on one data model source and one regular table source.
I also realized that regular pivot tables use ShowDetail while OLAP uses DrilledDown so I attempted to replace every ShowDetail with DrilledDown with no luck. The reason I added this data to a data model is the 3rd pivot table uses the discount count function.
The instructions on the other post mentioned the sheet with the pivot tables needed to be the furthest to the left, which TCSum is, but when I look in Visual Basic, I see Sheet3(TCSum), so I also tried replacing
Set wks = wkb.Sheets(1)
With
Set wks = wkb.Sheets(3)
And I figured why not try
Set wks = wkb.Sheets("TCSum")
I don't get any debug errors when I click the expand button on any of the rows, but unfortunately table 1 expands.
The 3 PivotTables on TCSum are named: TCByType, TCAge, TCAffected (which has the discount count if that matters).
