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

M

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.
 
J

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
 
M

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?
 
J

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
 
M

minimaster

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top