Help with Chart Macro.

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Excel 2010
Windows 7

Hello I have macro that I am trying to create that will do the following.

1. Select the chart on the tab. (there is only 1 chart named Chart 1)
2. Right click the chart and select "Select Data".
3. Remove series collect 1. (Which is removing the oldest month from the chart.)
4. Then add "Sept" series to the chart.
5. Have it use values of $AC$26:$AC$28 on the active sheet. (I cant figure out how to tell it to use that range on the active sheet).

But when I run the macro it does absolutely nothing!

Code:
Sub AddSeptFinal()'
' AddSeptFinal Macro
'


'
    
    ActiveSheet.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Name = "=""Sep"""
    ActiveChart.SeriesCollection(6).Values = "=CGO!$AC$26:$AC$28"
    Range("AM32").Select




End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi Troy

Something bad happened in your file.

You have 2 charts both named "Chart 1". Pretty confusing.

To add to the confusion the first chart has height and width 0, so you can't see it.

Run this code to make it visible:

Code:
Sub Test()
    
ActiveSheet.ChartObjects(1).Height = 200
ActiveSheet.ChartObjects(1).Width = 400

End Sub

And check around the cell E3. You'll see it there.

Delete this ghost chart and try the codes again.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Hi Troy

Something bad happened in your file.

You have 2 charts both named "Chart 1". Pretty confusing.

To add to the confusion the first chart has height and width 0, so you can't see it.

Run this code to make it visible:

Code:
Sub Test()
    
ActiveSheet.ChartObjects(1).Height = 200
ActiveSheet.ChartObjects(1).Width = 400

End Sub

And check around the cell E3. You'll see it there.

Delete this ghost chart and try the codes again.


Ok that helped on most of my tabs, there may be some other ghost charts on the ones that did not work on. However I need to figure out how to link the range: $AC$26:$AC$28 on the current tab not the CGO tab like it is in my code. How can I change this line to select the range on the active tab?

Code:
  ActiveChart.SeriesCollection(6).Values = "=CGO!$AC$26:$AC$28"
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

Code:
ActiveChart.SeriesCollection(6).Values = "='" & ActiveSheet.Name & "'!$AC$26:$AC$28"
 

Watch MrExcel Video

Forum statistics

Threads
1,114,607
Messages
5,548,954
Members
410,884
Latest member
melifreeman
Top