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.