Creating Multiple Charts using VBA

[Walser52]

Newbie here.

I'm trying to create multiple charts (i.e. different chart objects with one graph on each). I found a code on youtube that is linked in the comment that I'm modifying.
For now I want to be able to give StartRow which is a starting point and create a scatter plot of the next 700 rows. Then move to another column and plot that.
The code below does indeed create two chart objects but it is:

1. Creating multiple graphs on each chart object.
2. Selecting the data starting from Row 1 (instead of Row 5)
3. Selects x and y values for each graph in the same column.
4. The two chart objects are duplicates.

Sub AddCharts()


'https://www.youtube.com/watch?v=5_p8hltGY4s

Dim StartRow as Integer
StartRow = 5

Dim j As Integer 'columns

For j = 5 To 7
    With Worksheets("CVData").Shapes.AddChart.Chart
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
       With .SeriesCollection(1)
      
        .XValues = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(StartRow, 4), Cells(StartRow + 700, 4)).Address
        
        .Values = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(StartRow, j), Cells(StartRow + 700, j)).Address
        End With
    .HasLegend = False
  End With
Next j

End Sub

The data has the following form:

 

[Domenic]

I have amended your macro so that it creates a single chart, and then creates a series for each X and Y values (ie. Column D and Column E, Column D and Column F, and Column D and Column G. Although, based on the layout shown in your image, it looks like you'll have to adjust the columns.

Sub AddCharts()
 
    'https://www.youtube.com/watch?v=5_p8hltGY4s
   
    Dim StartRow As Integer
    StartRow = 5
   
    Dim j As Integer 'columns
   
    With Worksheets("CVData").Shapes.AddChart.Chart
        .ChartType = xlXYScatter
        .HasLegend = False
        For j = 5 To 7
            With .SeriesCollection.NewSeries
                .XValues = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(StartRow, 4), Cells(StartRow + 700, 4)).Address
                .Values = "=" & ActiveSheet.Name & "!" & _
                    Range(Cells(StartRow, j), Cells(StartRow + 700, j)).Address
            End With
        Next j
    End With

End Sub

However, your macro can be re-written as follows...

Sub AddCharts()
   
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
   
    Dim sourceRange As Range
    Set sourceRange = Range("D5:G" & lastRow)
   
    With Worksheets("CVData").Shapes.AddChart.Chart
   
        'remove existing series, if any
        While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Wend
       
        'set properties for chart
        .ChartType = xlXYScatter
        .HasLegend = False
       
        'add each series to chart
        Dim j As Long
        For j = 2 To 4
            With .SeriesCollection.NewSeries
                .XValues = sourceRange.Columns(1)
                .Values = sourceRange.Columns(j)
            End With
        Next j
       
    End With

End Sub

Hope this helps!