VBA to change single / specific series Y values range based on column for multiple charts

Question

I am fairly new to VBA. I have 100+charts with multiple data series on a single tab in excel. To create these I simply built one chart and manually copied over into each column. Each chart is the same except for data range for one (of the four) data series. For these series only, I want to change the YValues for each chart to refer to the next column (so chart1 Y values range is say A4:A100, chart 2 is B4:B100, chart 3 is C4:C100, and so on).

I was thinking a loop to select each chart, select series in question, and adjust the Y range.

Something like (doesn't work), where I would go up by 1 each time moving the range to next column

ActiveChart.SeriesCollection(1).YValues = RangeRange(Cells(4, i), Cells(100, i))

However, this only seems possible for Xvalues - there doesn't seem to be an equivalent Yvalue property.

ActiveChart.SeriesCollection(1).XValues = RangeRange(Cells(4, i), Cells(100, i))

Sounds simple but searched the internet far and wide and couldn't find how to edit specific data series range on a chart only the entire chart data range. Appreciate any help.

Thank you

Answer 1

instead of YValues, it's just Values...

      ActiveChart.SeriesCollection(1).Values = Range(Cells(4, i), Cells(100, i))

Answer 2

My final code: creates any number of charts by copying a template chart places new charts next to each other in a row sets new chart title based on the column reference adjusts range for specified chart series

Sub ChartGenerator()

Dim i As Integer
Dim cht As ChartObject
Dim dChart As Object
Dim NumCharts As Integer
Dim myRange As Range


Set myRange = ActiveSheet.Range("G3:DD3")

NumCharts = Application.WorksheetFunction.CountA(myRange)

For i = 1 To NumCharts

' copy template chart called "charttemplate"
 Set dChart = ActiveSheet.ChartObjects("charttemplate").Duplicate
 dChart.Select

' place it correctly
dChart.Top = ActiveSheet.ChartObjects("charttemplate").Top + ActiveSheet.ChartObjects("charttemplate").Height + 10
dChart.Left = (i - 1) * dChart.Width + ActiveSheet.ChartObjects("charttemplate").Left

' set chart title
dChart.Name = "newchart" & "" & i
dChart.Chart.HasTitle = True
dChart.Chart.ChartTitle.Text = "='" & ActiveSheet.Name & "'!R3C" & i + 6

' adjust series
ActiveChart.SeriesCollection(1).Values = Range(Cells(4, i + 6), Cells(10000, i + 6))

Next i

End Sub