Coding the series collections of a chart

[troy_lee]

I need some help with the code to assign values to a series collection
for charts. This is the first chart I have tried to do with VBA.

My first question is how do I properly set the SetSourceData Source?
This line is commented out below. All of the range arguments on this
line are verified to be correct (I created them in another piece of
code not shown here).

How do I treat the X axis? This should be SeriesCollection(1), right?

Also, am I assigning the range correctly for the values statements for
the series collections?

Finally, I want the 4th series collection (EOH) to be on a secondary Y
axis. The last line of code is what the macro recorder came up with
for this. Is this correct?

Any ideas are welcomed, including a more efficient way to do this.
Thanks for the help in advance.

'Create the Year to Year Summary Chart.
Sheets("Scorecard").Activate

Set chtYTY = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375,
Top:=75, Height:=225)

With chtYTY.Chart
.ChartType = xlLineMarkers
'.SetSourceData Source:=Sheets("Data").Range(rngXvalues,
rngShipped, rngIn, rngEOH),
'PlotBy:=xlColumns
End With

'Clear out all Series that Excel may have created by default.
With chtYTY.Chart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop

'Set up the X axis values
With .SeriesCollection.NewSeries
.XValues = "rngXvalues"
.Name = "Week Number"
End With

'First Y axis series.
With .SeriesCollection.NewSeries
.Name = "=Shipped"
.Values = "=Data!Range(rngShipped)"
.XValues = "=Data!rngXvalues"
End With

'Second Y axis series.
With .SeriesCollection.NewSeries
.Name = "In"
.Values = "rngIn"
.XValues = "rngXvalues"
End With

'Third Y axis series.
With .SeriesCollection.NewSeries
.Name = "EOH"
.Values = "rngEOH"
.XValues = "rngXvalues"
End With

'.Location Where:=xlLocationAsObject, Name:="Scorecard"
.HasTitle = True
.ChartTitle.Characters.Text = "Year to Year Ships"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week
Number"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Number
of Units"
.HasDataTable = False

'This is code for the secondary axis
.SeriesCollection(4).AxisGroup = 2

End With

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
PivotChart does most of the work.
Macro provided for touch up.
http://www.mediafire.com/file/a5zyz1uiqlg/03_25_09.xlsm