How to get the PivotTable report info that feeds another PivotTable report.

  • Thread starter Thread starter Toby Erkson
  • Start date Start date
T

Toby Erkson

A pivot table can get it's data from another pivot table as so:
Right click in the pivot table and select PivotTable Wizard
Click the < Back button
Click the next < Back button
Select the "Another PivotTable report or PivotChart report" radio button and
click Next >
Select the pivot table containing the same data and click Finish

What I want to get is the source pivot table for a pivot table. I guess I'm
looking for the "parent" of the "child" pivot table :-) How can I do this in
VBA?

Here's the macro recording of the above steps but it didn't help me for
reading the SourceData in other code I tried:
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
"PivotTable4"
End Sub

It appears to me that PivotTableWizard cannot be used to read the above
SourceData property from what I've tried :-( When I pull the SourceType it
returns xlExternal (2), not xlPivotTable (-4148) as I would expect. Ideally,
what
I would like to return is the sheet Name and pivot table SourceData value of a
pivot table
(pivot cache?), that way I could find the main pivot table that the other
pivot tables feed from.

TIA,
 
When you base a pivot table on another pivot table, it uses the same
source data as the original table. You can find that source data, e.g.:

Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
Debug.Print pt.PivotCache.SourceType
Debug.Print pt.PivotCache.SourceData
Next pt

But, I don't think you can determine which pivot table was used as the
source.
 
Yeah, I got that far (your example) but the source pivot table is what I'm
after. Rats. Interesting that with all the flexibility of Excel the info can
be set and used by the application but not by the user.

Thanks Debra,
 
Back
Top