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" wrote:

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in message
...
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