Help with Chart Macro.

[Troy1]

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!

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]

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:

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.

 

[Troy1]

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:

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?

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

 

[Andrew Poulsom]

Like this?

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

 

[Troy1]

Like this?

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

Worked great! Thank you so much!