Pivot Chart: referencing the underlying Pivot Table

[tyemm]

When I look today at the pivot chart I created yesterday, I have a problem "finding" the pivot table from which I created it. I cannot right-click, or find the "source data" or "series" readily at hand. Indeed even recreating what I have done is tricky, making documentation a problem.

What is the best way to keep track of this table, so that I don't end up accumulating material I don't need/want? Is there a tutorial on this aspect precisely? For example, I'd also like to embed a pivot chart in a cell, so that I can use the camera tool and display the chart in a dashboard, etc.

Thanks,
Tom

 

[SkipVought]

Hi,

Do you have multiple PivotTables in your workbook?

If so, do each of them point to a different data source?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This may help you "find" the info. Paste in a module and run from the Tools > Macro > Macros menu ...

Sub PivotChartLocations()
    Dim ch As Chart
    For Each ch In Charts
        With ch.PivotLayout.PivotTable.TableRange1
            MsgBox ch.Name & ":" & .Parent.Name & "!" & .Address
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyemm]

Thanks Skip--
When I created that Module and tried to run it, I get:
Object variable or With block variable not set (Run-time error '91'). And the answer to your first question is: I'm not sure--but the fact that one of them comes up as PivotTable3 (not so named by me!) suggests that I have or at least had as many as three. Not intentionally, and I'm not sure if even necessary. My original raw data table is singular, and not so special. But, as a for instance, I have the Time of events (e.g. 07:13) and I'd like to sort by Shift (shift one = 7AM to 3PM, etc.) and I did this (somehow) a week ago, and I have a nice enough chart, but now I cannot retrace my steps and it feels like Hansel & Gretel!

 

[SkipVought]

try this instead...

Sub PivotChartLocations()
    Dim ch As Chart
    For Each ch In ActiveWorkbook.Charts
        With ch.PivotLayout.PivotTable.TableRange1
            MsgBox ch.Name & ":" & .Parent.Name & "!" & .Address
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]