Macro to create chart

  • Thread starter Thread starter JGeorge
  • Start date Start date
J

JGeorge

I am trying to create a chart based on the fields selected in the
worksheet. The chart loads fine, but I am getting this error:

Runtime error 1004
Method 'SetSourceData' of Object '_Chart' failed

Here's the Macro:
Sub Macro1()
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="PMS"

----Line I get error on:
ActiveChart.SetSourceData
Source:=Application.ActiveWindow.ActiveCell, PlotBy:= _
xlColumns


ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = = "Title"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = =
"Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Data = "
End With
End Sub


Does anyone have any idea why I'm getting this error? This is in Excel
2003. Thank you in advance!

Jill
 
Jill,

You are telling Excel to plot a chart based upon a single data point...
"ActiveCell". That is my guess as to what is causing the problem.
Try using "Selection" or the specific range with the data.

Regards,
Jim Cone
San Francisco, USA


I am trying to create a chart based on the fields selected in the
worksheet. The chart loads fine, but I am getting this error:

Runtime error 1004
Method 'SetSourceData' of Object '_Chart' failed

Here's the Macro:
Sub Macro1()
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="PMS"

----Line I get error on:
ActiveChart.SetSourceData
Source:=Application.ActiveWindow.ActiveCell, PlotBy:= _
xlColumns


ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = = "Title"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = =
"Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Data = "
End With
End Sub


Does anyone have any idea why I'm getting this error? This is in Excel
2003. Thank you in advance!

Jill
 
Jill -

As soon as you create the chart, it becomes the active sheet, and
there's no active cell.

Sub Macro1()
Dim ChartRange as Range

Set ChartRange = ActiveCell

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, _
TypeName:="PMS"

ActiveChart.SetSourceData _
Source:=ChartRange, _
PlotBy:=xlColumns

'' etc.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top