RE: how can I get the worksheet with the pivottable sourcedata when wo

Tech-Archive recommends: Speed Up your PC by fixing your registry

You don't need the parent. pt is already the chart (a chart is equivalent ot
a sheet). Look at the VBAProject window. You'll see the sheets and the
charts are a the save level in the window.

Set pt = ActiveChart.PivotLayout.PivotTable ' no problem to
' get the pivottable
data = pt.SourceData

"minimaster" wrote:

With VBA I like to add some shapes/text to a Pivotchart. The text
items are part of the pivottable sourcedata table. With VBA I need to
identify the work*** which contains the sourcedata. I'm wondering
whether is any easy way to get a handle to this sourcedata work***.
If not I would need to analyze the sourcedata string and write a
function that would be using some text functions to retrieve the
work*** name out of the sourcedata string. This would get even more
complicated when the sourcedata string is a dynamic named range.
Anybody an idea how this could be done in a more easy way

Dim pt As PivotTable
Dim ws As Work***

Set pt = ActiveChart.PivotLayout.PivotTable ' no problem to
get the pivottable

Set ws = Range(pt.SourceData).Parent
' I believe this does not work because the work*** is not identified
for the range method. Chicken and egg situation.

.

Quantcast