Displaying series list from each chart sheet in workbook

[Guest]

I have the following macro which reviews embedded charts on worksheets and
lists out the series information on a separate worksheet. I want to do the
same thing for the charts that are CHART sheets, rather than worksheets.
What do I need to change?

Sub ChartSeriesList()
Dim sht As Worksheet
Dim objCht As ChartObject
Dim xVal, yVal, seriesformula
Dim k, i
Dim lastrow
Dim CurBook

CurBook = Application.ActiveWorkbook.Name
ActiveWorkbook.Unprotect
Worksheets.Add.Name = "SeriesList"
'lastrow = Worksheets("SeriesList").Cells(Rows.Count, "a").End(xlUp).row
lastrow = 0

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
For Each objCht In sht.ChartObjects
With objCht.Chart
For k = 1 To .SeriesCollection.Count
'xVal = .SeriesCollection(k).XValues
'yVal = .SeriesCollection(k).Values
seriesformula = .SeriesCollection(k).Formula
'Debug.Print sht.Name; " has chart; "; .Parent.Name; _
".; Series"; k; "; formula"; seriesformula
Workbooks(CurBook).Worksheets("SeriesList").Range("a" & lastrow + 1).Value =
sht.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("b" & lastrow + 1).Value =
..Parent.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("c" & lastrow + 1).Value = k
Workbooks(CurBook).Worksheets("SeriesList").Range("d" & lastrow + 1).Value =
"'" & seriesformula
lastrow = lastrow + 1
Next k
End With
Next
Next sht
End Sub

Thanks in advance,

Barb Reinhardt

 

[Tom Ogilvy]

Sub ChartSeriesList()
Dim cht As Chart
Dim xVal, yVal, seriesformula
Dim k, i
Dim lastrow
Dim CurBook

CurBook = Application.ActiveWorkbook.Name
ActiveWorkbook.Unprotect
Worksheets.Add.Name = "SeriesList"
'lastrow = Worksheets("SeriesList").Cells(Rows.Count, "a").End(xlUp).row
lastrow = 0

For Each cht In ActiveWorkbook.Charts
cht.Activate
With cht
For k = 1 To .SeriesCollection.Count
'xVal = .SeriesCollection(k).XValues
'yVal = .SeriesCollection(k).Values
seriesformula = .SeriesCollection(k).Formula
'Debug.Print sht.Name; " has chart; "; .Parent.Name; _
".; Series"; k; "; formula"; seriesformula
Workbooks(CurBook).Worksheets("SeriesList").Range( _
"a" & lastrow + 1).Value = cht.Name
Workbooks(CurBook).Worksheets("SeriesList").Range( _
"b" & lastrow + 1).Value = cht.Name
Workbooks(CurBook).Worksheets("SeriesList").Range( _
"c" & lastrow + 1).Value = k
Workbooks(CurBook).Worksheets("SeriesList").Range( _
"d" & lastrow + 1).Value = "'" & seriesformula
lastrow = lastrow + 1
Next k
End With
Next cht
End Sub

 

[Guest]

Thanks! What do I need to do with this so that it adds the worksheet at the
beginning of the workbook?

Worksheets.Add.Name = "SeriesList"

Also, it appears that I have series numbers that are missing. How do I
address that?

 

[Guest]

Tom,

I'm getting the following error:

Unable to get the formula property of the series collection (I think that's
it)

for one of the series. When I view the series formulas in the command line,
that series isn't even listed, but it is listed when I view it using the
source data selection. I suspect this is unrelated, but it is a problem.
What should I do?

Thanks,
Barb

 

[Tom Ogilvy]

Worksheets.Add(Before:=Sheets(1) _
).Name = "SeriesList"


I tested it with an XY Scatter Plot with 5 series, a Line Chart with 5
series and an XY Scatter Plot with 1 series and they were all reported. I
don't see anything obvious in your code that would miss a series.

 

[Tom Ogilvy]

If you select the individual series in the chart, does it have a formula in
the formula bar?