Macro Chart Creation help Please!

DWilliams12

New Member
Joined
Nov 2, 2017
Messages
1
[FONT=&quot]Hi, [/FONT]
[FONT=&quot]I am trying to create a macro to create a graph on some data from the spreadsheet. [/FONT]
[FONT=&quot]To do this I have recorded the macro of the graph I am trying to create being raised manually. [/FONT]
[FONT=&quot]The table I am trying to show data from is quite obscure and I am having to edit the graph plotting points after the chart has been generated.[/FONT]
[FONT=&quot]the macro is as follows: [/FONT]
Sub GIANDO()
'
' GIANDO Macro
'
'
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range( _
"'Yearly Averages by Month'!$A$1:$AL$390")
ActiveChart.FullSeriesCollection(33).Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=""Goods In"""
ActiveChart.FullSeriesCollection(1).Values = _
"='Yearly Averages by Month'!$E$4,'Yearly Averages by Month'!$L$4,'Yearly Averages by Month'!$S$4,'Yearly Averages by Month'!$Z$4"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=""Goods Out"""
ActiveChart.FullSeriesCollection(2).Values = _
"='Yearly Averages by Month'!$F$4,'Yearly Averages by Month'!$M$4,'Yearly Averages by Month'!$T$4,'Yearly Averages by Month'!$AA$4"
ActiveChart.FullSeriesCollection(2).XValues = _
"='Yearly Averages by Month'!$D$2,'Yearly Averages by Month'!$K$2,'Yearly Averages by Month'!$R$2,'Yearly Averages by Month'!$Y$2"
End Sub

[FONT=&quot]The issue i am having is, to change the data points it is trying to activate the chart first <see red line above>. The spreadsheet is set up so that any charts that are created, are then deleted once you click off of them, so there can only ever be 1 chart open at a time. So when it is always looking for "Chart 5", it very rarely exists![/FONT]
[FONT=&quot]Is there any way to tell to to activate the open chart or rename it regardless of it's designation? [/FONT]
[FONT=&quot]Thanks, [/FONT]
[FONT=&quot]Dave [/FONT]
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi and welcome to the MrExcel Message Board.

First question: Will it work without that line?

If it does need to be then then try replacing it with:
Code:
ActiveChart.Activate
but I can't help thinking that if the chart is already active then asking Excel to activate it is not going to make a difference.


Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,089,891
Messages
5,411,055
Members
403,338
Latest member
LynBrown

This Week's Hot Topics

Top