Create Chart - 12 series offset one column from the last

[wruwtrix]

I'm interested in creating a scatter plot of 12 series as a new chart in its own sheet. The Y values are the same for each series, but the X Values may differ and are in consecutive columns in my data sheet (DataAll). The names for each series are in consecutive rows in a setup sheet (SetupAll)

I want to do this several times for different sets of series, so I want a macro that is easily changed. I am getting an Object Required error '424' at line

.SeriesCollection(2).XValues = NewChart.SeriesCollection(1).XValues.Offset(0, 1)

Any idea why I am getting this error? I have included my code with the first 3 data series to be added.

Also - will this chart update automatically if the X Values are updated?

Thanks!
V. Hagberg

Sub AddChartSheet()
'Add a new XY Scatter Plot chart as a new sheet

Dim NewChart As Chart
Dim ChartName, SheetName, XAxis, YAxis As String

'SheetName should be of the form XValue-Yvalue on BinSort.  Shorten if necessary.  ie "qt-vs on Bq"
'ChartName will be the title of the new chart

    SheetName = "qt-vs on Bq"
    ChartName = "qt (tsf) vs. vs (ft/s)"
    XAxis = "qt (tsf)"
    YAxis = "vs (ft/s)"
    
'Add chart - no changes should be necessary in this section
    Set NewChart = Charts.Add
    With NewChart
        .ChartType = xlXYScatter
        .Location where:=xlLocationAsNewSheet, Name:=SheetName
        .HasTitle = True
        .ChartTitle.Text = ChartName
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XAxis
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YAxis


'Add 12 data series - Must input ranges for first series.  XValues should be bins of values from
 'DataAll sheet and must be consecutive columns with the leftmost column entered as series 1.
'Values should be shear or comp. velocities
'Series Name should be defined in SetupAll sheet by the Bin definitions.  Names must be
 'in consecutive rows with the series 1 name as the topmost row.
 
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = "=DataAll!CR11:CR17905"
        .SeriesCollection(1).Values = "=DataAll!N11:N17905"
        .SeriesCollection(1).Name = "=SetupAll!D3"

        .SeriesCollection.NewSeries
        .SeriesCollection(2).XValues = NewChart.SeriesCollection(1).XValues.Offset(0, 1)
        .SeriesCollection(2).Values = "=DataAll!N11:N17905"
        .SeriesCollection(2).Name = NewChart.SeriesCollection(1).Name.Offset(1, 0)
        
        .SeriesCollection.NewSeries
        .SeriesCollection(3).XValues = NewChart.SeriesCollection(2).XValues.Offset(0, 1)
        .SeriesCollection(3).Values = "=DataAll!N11:N17905"
        .SeriesCollection(3).Name = NewChart.SeriesCollection(2).Name.Offset(1, 0)
        
End With
End Sub