SetSourceData Failure

[Guest]

I've created a macro that creates charts (4 total, on one sheet) based on
values found within a worksheet. It pulls data from a MS Project plan and
places it into excel. This data is then used to create the charts. The
macro works fine when I run it for the first time but I get a "Method
'SetSourceData of object'_Chart failed" error if i run it twice in the same
session.

The chart values are on worksheet #3 and the charts are placed in worksheet
#4.

My code is as such:

Dim CWeek As Range, CBCWP As Range, CBCWS As Range, CACWP As Range
Dim CCV As Range, CCPI As Range, CSV As Range, CSPI As Range......(etc.)

Dim co2SD As Variant, co3SD As Variant, co4SD As Variant, co5SD As Variant

Dim co2 As ChartObject, co3 As ChartObject, co4 As ChartObject, co5 As
ChartObject


'Set up the Chart Ranges based on the dates selected to run the report
Set CWeek = ActiveSheet.Range(Cells((20 + MonthVar), 1), Cells((21 +
MonthVar + WeekVar), 1))
Set CBCWP = ActiveSheet.Range(Cells((20 + MonthVar), 3), Cells((21 +
MonthVar + WeekVar), 3))
Set CBCWS = ActiveSheet.Range(Cells((20 + MonthVar), 2), Cells((21 +
MonthVar + WeekVar), 2))
Set CACWP = ActiveSheet.Range(Cells((20 + MonthVar), 4), Cells((21 +
MonthVar + WeekVar), 4)) (etc...)


'Set Unions for each chart range for source data

Set co2SD = Union(CWeek, CCPI, CCPIUCL, CCPIAvg, CCPILCL, CCPIUSL,
CCPITarget, CCPILSL)
Set co3SD = Union(CWeek, CSPI, CSPIUCL, CSPIAvg, CSPILCL, CSPIUSL,
CSPITarget, CSPILSL)
Set co4SD = Union(CWeek, CCV, CCVUCL, CCVAvg, CCVLCL, CCVUSL,
CCVTarget, CCVLSL)
Set co5SD = Union(CWeek, CSV, CSVUCL, CSVAvg, CSVLCL, CSVUSL,
CSVTarget, CSVLSL)

xlBook.Worksheets(4).Select
xlBook.Worksheets(4).Name = "Report Graphs"

' Create 1st Chart showing the Cost Performance Index

Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700, 380)
co2.Chart.ChartType = xlLineMarkers
co2.Chart.SetSourceData Source:=co2SD, _
PlotBy:=xlColumns
co2.Chart.Location Where:=xlLocationAsObject, Name:="Report Graphs"
With co2.Chart
.HasTitle = True
.ChartTitle.Characters.Text = "CPI Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Index"
End With

******************************************************

Again, the macro runs perfectly fine the first time i run it. The error
occurs when I rerun the macro. Any and all help would be great. Thanks.

 

[Jon Peltier]

Does it help if you declare co2SD and the others as Ranges? Also, are
any of the ranges completely devoid of plottable data (i.e., blank or
errors)?

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