Refreshing PivotTables that use a SSAS OLAP cube data source

[stuart321]

Hi,

I have multiple PivotTables connected to a SSAS OLAP cube. I created these multiple PivotTables by starting with one PivotTable, then copying/pasting that PivotTable in the same worksheet to create the new PivotTables. I'm using Model F referenced in the following link: Pivottable data connection models - Pixcels.nl

When I run the code below:

Sub GetThePivotCacheInfo()
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
Debug.Print "QueryType: " & pc.QueryType & ", PivotCacheSourceType: " & pc.SourceType & ", PivotCacheIndex: " & pc.Index
Next pc
End Sub

I get a QueryType value of 5, PivotCacheSourceType value of 2, however the PivotCacheIndex is unique for each of these PivotTables. I would have expected the PivotTables to all be using the same PivotCache since I created the PivotTables by copying from a PivotTable (and all PivotTables are in the same worksheet).

My question is this... even though the PivotCache indexes are different for each of the PivotTables, will all the PivotTables get refreshed if I refresh just one of the PivotTables?

I believe the answer is yes, but I would like to confirm. (I think the answer is yes because if I leave the 'Autofit column widths on update' property on, I notice all the PivotTable column widths get adjusted with updating just one PivotTable.)

I appreciate any insight on this - Thanks!