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 worksheet which contains the sourcedata. I'm wondering
whether is any easy way to get a handle to this sourcedata worksheet.
If not I would need to analyze the sourcedata string and write a
function that would be using some text functions to retrieve the
worksheet 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 Worksheet

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 worksheet is not identified
for the range method. Chicken and egg situation.

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 worksheet which contains the sourcedata. I'm wondering
whether is any easy way to get a handle to this sourcedata worksheet.
If not I would need to analyze the sourcedata string and write a
function that would be using some text functions to retrieve the
worksheet 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 Worksheet

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 worksheet is not identified
for the range method. Chicken and egg situation.

I'm not sure how that will help me to identify the worksheet name with
the source data. May be I don't see the forest because there are so
many trees.
Your suggestion "data=pt.sourcedata" only provides me with a string,
or do I' missing something here?

Here is more inof to help you solve your problem. the main problem is
SourceData return r1C1 format and a Range() statement requires regular
addressing. See code below


Sub test()

Set pt = ActiveChart.PivotLayout.PivotTable ' no problem to
' get the pivottable
DataRangeR1C1 = pt.SourceData
DataRange = Application.ConvertFormula( _
Formula:=DataRangeR1C1, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
Set data = Range(DataRange)
For Each cell In data
'enter your code here
Next data
End Sub

"minimaster" wrote:

I'm not sure how that will help me to identify the worksheet name with
the source data. May be I don't see the forest because there are so
many trees.
Your suggestion "data=pt.sourcedata" only provides me with a string,
or do I' missing something here?

Thx a lot, after conversion to A1 notation it even works fine with
named dynamic ranges as pivot table source data. Problem solved.