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]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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,
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top