Macro Chart Creation help Please!

[DWilliams12]

[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]

 

[RickXL]

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:

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,