How to identify Chart Source data as range?



I have a 'normal' excel chart on a Chart sheet (Cht1)
Source data is coming from 1 of 3 pivot tables all on the same worksheet
(wks1) using vba code.
Userform on chart sheet has 3 option buttons to pass a pivot table name to
'updateChart' code.
All working OK
When the user first opens the file I would like the appropriate option
button to be set to True according to which pivot table the source data was
coming from on the previous fileSave and now being displayed as the active
Would like to identify the chart source data rngCSD range in code and test
set rng = intersection(wks.PivotTables(pt1).DataBodyRange, rngCSD)
if not rng is nothing then OptBtn1 = True, etc
How do I identify the source data range (in code)

then test

Gary Brown

Hopefully these two pieces of information can point you in the right

The Chart Source:
ActiveChart.SeriesCollection(1).Formula = _

The Pivot Table Source:


Thanks Gary (i made sure to click the 'Yes' button)
Here's my new code making use of your advice:

Sub GETptNm()
Dim txt As String
Dim SourceRng As Range
Dim ptRng As Range
Dim rng As Range
Dim pt As PivotTable
Dim i As Integer
Dim FirstComma As Integer
Dim n As Integer

'// get cell address for SERIES source data range within Pivot table
'// from seriesCollection(1).Formula
'// example: "=SERIES(PTs!$B$7,PTs!$A$8:$A$52,PTs!$B$8:$B$52,1)"
txt = ActiveChart.SeriesCollection(1).Formula
txt = Replace(txt, "=SERIES(", "")
FirstComma = InStr(txt, ",")
txt = Left(txt, FirstComma - 1) ' range address now identified

'// Capture the range to enclose entire Pivot table
Set SourceRng = Range(txt).CurrentRegion

'// Test identified cell location against each pivot table
n = PTs.PivotTables.Count '// 'PTs' is code name for sheet
For i = 1 To n
Set pt = PTs.PivotTables(i)
Set ptRng = pt.TableRange2
Set rng = Intersect(SourceRng, ptRng)
If Not rng Is Nothing Then
ptNm = pt.Name
Exit For
End If
Next i
End Sub


Thanks p,
I'll take a look at that

p45cal said:
Just a note of caution; you've taken the first element in the series
formula which is the Name of the series, often a single cell, but more
importantly, it can be nothing, the same goes for the second element
which are x axis categories/values, the third element is the y values
range, which may be the one to go for because a series really should
have some y-values! Some charts have non-contiguous ranges for y-values,
so there may be more uncertainty there.

There's more on more reliably finding a chart's source range (including
a function) in John Walkenbach's book Excel 2002 Power Programming with
VBA, on page 542, which is surprisingly, but happily, available on the
web 'here' ( It took a minute or two to load
- I hope it's legal.

Thanks Gary (i made sure to click the 'Yes' button)


p45cal's Profile:
View this thread:


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