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

 

[Andrew Poulsom]

Have you tried putting a breakpoint in your macro and stepping through it? How are you calling the macro? If from another macro have you set On Error Resume Next somewhere in it?

 

[Troy1]

The macro is its own sub routine. I have stepped through the macro, it appears that it is selecting the chart but not calling the "Select Data" function to allow it to remove seriescollection1 and adding seriescollection6. Basically the macro is just selecting the chart and then deselecting the chart.

 

[Andrew Poulsom]

Your code worked for me when I tried it. I plotted W26:AB28 as six series. Running the macro deleted the first series (W6:W28) and added AC26:Ac28 as series 6.

 

[pgc01]

Hi Troy

Please try this (part of your) code and see if it does not delete the first series in the first chart:

Sub Test()
    
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Delete

End Sub

 

[Troy1]

Hi Troy

Please try this (part of your) code and see if it does not delete the first series in the first chart:

Sub Test()
    
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Delete

End Sub

Hey I tried this and it did not delete the first series. The macro appears to select the chart but then does nothing. I am not sure what is going on .

 

[Troy1]

Here I have uploaded an example spreadsheet.

https://drive.google.com/file/d/0B7ZAZgflZvezTkk2T19UdFZFWDg/edit?usp=sharing

 

[Andrew Poulsom]

I am unable to download that workbook - something about too many views/downloads recently.

 

[Troy1]

Try this link:

https://www.dropbox.com/s/ynva3rdr89026n5/Example%20Chart%20Macro.xlsx?dl=0

 

[Andrew Poulsom]

That's an xlsx, which can't contain macros.