Creating Multiple Charts using VBA

Walser52

New Member
Joined
Jul 19, 2021
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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:
1626751260775.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,825
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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...

VBA Code:
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!
 
Solution

Forum statistics

Threads
1,147,633
Messages
5,742,235
Members
423,716
Latest member
melpotter22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top