Hi everyone,

My macro will run perfect the first time I run it. If I run it again,
without closing the excel file that was created by my first macro, I get an
error message. If I close the excel file and then run the macro, it again
works fine. Why is this and how can I fix it?

Details:
The error message I get is "Method SetSourceData of...failed". The macro
pulls data from a ms project file and places it into excel. I then use this
data to create charts. The error occurs when I create my first chart.

Code:

Dim CWeek As Range, CBCWP As Range, CBCWS 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
' This data is on the 3rd chart

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

' Go to 4th worksheet and display charts

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

' ERROR OCCURS HE
co2.Chart.SetSourceData Source:=co2SD, PlotBy:=xlColumns

co2.Chart.Location Whe=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