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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 2, 2014
Messages
149
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887

ADVERTISEMENT

Hi Troy

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

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

End Sub
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Hi Troy

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

Code:
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

Board Regular
Joined
Jul 2, 2014
Messages
149

ADVERTISEMENT

Here I have uploaded an example spreadsheet.

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I am unable to download that workbook - something about too many views/downloads recently.
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Try this link:

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,484
Members
430,358
Latest member
zzc1128

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
Top