Create A chart Via Macro

**Philb1** · 09-14-2011, 07:07 AM

I want to be able to create a chart via macro. I can do that ok. The trouble is when I use the macro to create another chart, it uses the data from the last chart. I selected the data before recording the macro thinking that it wouldn't record preselected cells. It seems it does.
Any ideas of how to overcome this?
Thanks

**mrice** · 09-14-2011, 07:13 AM

Have a look at the code that you have recorded and replace the characters which specify the range that you are plotting with the word Selection.

Suggest posting a copy of the recorded code if you need further help.

**Philb1** · 09-14-2011, 07:39 AM

Hi mrice
I've attached a file showing the symptoms. Cntrl M runs the macro
I've no idea what I'm looking for when in the edit screen
Thanks

**mrice** · 09-14-2011, 07:49 AM

Try

Set MyPlotRange = Selection
ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlCylinderColClustered
    ActiveChart.SetSourceData Source:=MyPlotRange 
    ActiveChart.Parent.Cut
    Sheets("Sheet2").Select
    Range("D4").Select
    ActiveSheet.Paste
    Range("C4").Select