Macro to create chart

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


"JGeorge" <jillegeorge@gmail.com> wrote in message news:1123802145.712537.55320@g49g2000cwa.googlegroups.com...
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/
_______

JGeorge wrote:

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