Macro to create chart

[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

 

[Jim Cone]

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

 

[Jon Peltier]

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/
_______