Displaying series list from each chart sheet in workbook
 

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

Displaying series list from each chart sheet in workbook
 

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

Displaying series list from each chart sheet in workbook
 

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?

"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

Displaying series list from each chart sheet in workbook
 

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

Displaying series list from each chart sheet in workbook
 

Worksheets.Add(Befo=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.

--
Regards,
Tom Ogilvy

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

"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

Displaying series list from each chart sheet in workbook
 

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

--
Regards,
Tom Ogilvy

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