How to generate multiple line charts VBA

Requirement:

The user is trying to automatically generate multiple charts using VBA.

Solution:

Use this, with the original data layout:

Code:

Sub CreateCharts()
    Dim ws As Worksheet
    Dim ch As Chart
    Dim NumCharts As Integer, ChartName As String, ChartTitle As String, i  As Integer
     
    Set ws = Sheets("Chart_data")
     
    NumCharts = WorksheetFunction.CountA(ws.Rows(2))
     
    For i = 3 To NumCharts Step 2 '2 columns of data per chart
        ChartName = ws.Cells(2, i) '"chrt" & Range(Col1 & 2)
        ChartTitle = ws.Cells(2, i) 'Range(Col1 & 2)
        Set ch = Charts.Add
        With ch
            .ChartType = xlLine
            .SetSourceData Source:=ws.Range(ws.Cells(3, i), ws.Cells(27, i + 1)), _
                                            PlotBy:=xlColumns
            .SeriesCollection(1).XValues = ws.Range("B4:B27")
            .SeriesCollection(2).XValues = ws.Range("B4:B27")
            .Name = ChartName
            .HasTitle = True
            .ChartTitle.Characters.Text = ChartTitle
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Units"
        End With
    Next i
     
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by rory.