Plot multiple datasets on the same chart with VBA

Question

I have a dataset that looks like this:

x_val  set1 set2 set3 set4 set5 ...
1.1    12   36   12   23   33   ...
1.2    44   22   11   1    13   ...
1.3    54   5    56   56   34   ...
1.4    1    2    6    12   33   ...

I want to plot (setX vs x_val) and (setY vs x_val) in the same chart. Most importantly, I want to be able to plot any number of sets vs x_val on the same chart. The code I have doesn't really work. It results in this:  - in this case using F13:F15 to create 3 plots.

' Step 1: Load data from all sheets specified in range F13:Fn
Dim sheetNames As Range
Set sheetNames = ThisWorkbook.Sheets("Coefs").Range("F13:F15")

Dim dataRanges As Collection
Set dataRanges = New Collection

Dim i As Long
For i = 1 To sheetNames.Rows.Count
    Dim sheetName As String
    sheetName = sheetNames.Cells(i, 1).value
    
    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Sheets(sheetName)
    
    Dim lastRow As Long
    lastRow = dataSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    Dim xColumn As Integer
    xColumn = ThisWorkbook.Sheets("coefs").Range("G1").value
    
    Dim yColumn As Integer
    yColumn = ThisWorkbook.Sheets("coefs").Range("G2").value
    
    Dim dataRange As Range
    Set dataRange = dataSheet.Range("B2:C" & lastRow)
    dataRanges.Add dataRange.Columns(xColumn)
    dataRanges.Add dataRange.Columns
Next i
' Step 2: Create the chart and plot the data
Dim chartSheet As Worksheet
Set chartSheet = ThisWorkbook.Sheets("Coefs")

Dim chartObject As chartObject
Set chartObject = chartSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=300, Height:=300)

With chartObject.Chart
    .ChartType = xlXYScatter
    
    ' Add each data range to the chart as a new series
    Dim j As Long
    For j = 1 To dataRanges.Count Step 2
 
        Dim seriesRange As Range
        Set seriesRange = Range(dataRanges(j), dataRanges(j + 1))
        
        Dim series As series
        Set series = .SeriesCollection.NewSeries
        
        series.Values = seriesRange.Columns(2)
        series.XValues = seriesRange.Columns(1)
    Next j
    
    ' Set the chart axis titles and chart title
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Text = "XX"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Text = "YY"
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Text = "TTITLE"
End With
End Sub

In range F13:F14 (can be F13:Fn), I have the sheet names from which I want to fetch the data, so It's more like an x_val and a setX column for each of the sheets. In G1 and G2 I can choose which columns from the dataset I want to fetch (2 is x_val, 3 is set1, and so on). The rest is, well, garbage.

The error is probably in the lines

    dataRanges.Add dataRange.Columns(xColumn)
    dataRanges.Add dataRange.Columns

but I don't know enough VBA to fix it

Answer 1

Try this:

Sub PlotData()

    Dim wb As Workbook, chartSheet As Worksheet, chartObject As chartObject, cht As Chart
    Dim c As Range, ws As Worksheet, rngX As Range, rngY As Range, xCol As Long, yCol As Long
    
    Set wb = ThisWorkbook
    
    Set chartSheet = wb.Sheets("Coefs")
    Set chartObject = chartSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=300, Height:=300)
    Set cht = chartObject.Chart
    cht.ChartType = xlXYScatter
    
    'which columns are beingp lotted?
    xCol = chartSheet.Range("G1").Value
    yCol = chartSheet.Range("G2").Value
    'loop over the source sheets and add the series
    For Each c In chartSheet.Range("F13:F15").Cells
        Set ws = wb.Worksheets(c.Value)
        Set rngX = ws.Range(ws.Cells(2, xCol), ws.Cells(Rows.Count, xCol).End(xlUp))
        Set rngY = rngX.EntireRow.Columns(yCol)

        Debug.Print "Plotting", ws.Name, rngX.Address, rngY.Address
        With cht.SeriesCollection.NewSeries
            .XValues = rngX
            .Values = rngY
        End With
    Next c
    
    With cht
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "XX"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "YY"
        .HasLegend = True
        .HasTitle = True
        .ChartTitle.Text = "TTITLE"
    End With

End Sub