B
Barb Reinhardt
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
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