Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Displaying series list from each chart sheet in workbook
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying items from a column list in another sheet? pieman3 Excel Worksheet Functions 18 April 7th 09 03:03 PM
Chart Series Formats lost when list re -sorted Fullam Excel Discussion (Misc queries) 1 June 6th 06 10:44 AM
how to turn of a series in data sheet of a power point chart using macro chepyalarr Excel Programming 1 February 2nd 06 07:06 PM
Select chart series using dropdown list Alex Delamain Charts and Charting in Excel 2 August 31st 05 07:35 PM
Hiding or Displaying Series In a Chart SPYREN Charts and Charting in Excel 1 November 27th 04 12:49 PM


All times are GMT +1. The time now is 08:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement