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

I'm not an MVP-level expert by any means, but since no one else has replied
yet, I'll give you what I would do if I were hunting down something like
this.

Given the nature of the error, it seems like your data source, "co2SD", is
empty of data, or at least doesn't have what the Method is looking for.
Since it works the first time but not the second, my thought is that
something is changing in at least one of the ranges you Union, and renders
that object unusable. Also, you don't show the creation of all the ranges
in your Union statement - either you have cut them out of your post to save
space, or they are created in another procedure that is not shown here. You
also don't show what you do with them when you're done with them.

(BTW, if the result of Union is a Range object, and you Union Ranges, why
Dim everything as Variant rather than as Range?)

Here's how I would try to track it. Before the Union statement, set
Debug.Print statements for something about each of those ranges - maybe
something like
Debug.Print Range("CWeek")Rows.Count
Debug.Print Range("CWeek").Columns.Count
etc. Then do the same for co2SD after the Union. Step through it either
using F8, or F5 with break points, and watch BOTH the Immediate and the
Locals windows. The Locals window will tell you if the variable is getting
set at all, and give you useful information about it. The Immediate window
will show your Debug.Print results to tell you if your range is useful to
your Union statement.

Then step through it again and see what is different. I would suspect one
of the ranges in the Union is not getting set properly the second time
because your macro affects it and the method used to create it is not
rerunning without restarting Excel.

HTH
Ed

"Please Help" <Please wrote in message
...
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