About Us

David

Thanks p,
I'll take a look at that

"p45cal" wrote:

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' (http://tinyurl.com/y24qhol). It took a minute or two to load
- I hope it's legal.

David;706130 Wrote:

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

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196879

http://www.thecodecage.com/forumz

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using a named range as a data source for a chart	MichaelR	Charts and Charting in Excel	4	June 16th 08 04:15 AM
Using a Named Range for Data Source In A Chart	JoeP	Charts and Charting in Excel	2	April 25th 07 02:33 AM
Chart: Use range name for source data	sk[_2_]	Excel Programming	2	July 6th 05 08:17 PM
Chart source data range	Don Rouse	Excel Programming	2	March 23rd 05 06:17 PM
How to identify source or location of MAX_VALUE in range	Phil	Excel Worksheet Functions	5	March 7th 05 11:21 PM

Menu

About Us