how can I get the worksheet with the pivottable sourcedata whenworking with a pivotchart ?

[minimaster]

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.

 

[Joel]

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]

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?

 

[Joel]

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]

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