Using a macro to add chart series

[pksmith]

Hello all,

I am trying to write a macro that creates an X/Y scatter chart. The series will be named ranges (ex: Series X Values ='GraphTool.xlsm'!XValues1'; Series Y Values ='Graph Tool.xlsm'!YValuesTemp1".

I have several named ranges that I plan to use a for loop to insert them as series as needed (ex. several ranges that will be X values, several that will be Y values)

I would like to know how I create a new chart and add series using the named ranges in a macro.

Thank you for your help!

 

[pgc01]

Hi

This is an example that creates a XY chart in Sheet1, then adds 2 series using named ranges in Sheet1.

For this test I used the named ranges in Sheet1:

XValues1 =Sheet1!$B$4:$B$7
XValues2 =Sheet1!$E$4:$E$9
YValues1 =Sheet1!$C$4:$C$7
Yvalues2 =Sheet1!$F$4:$F$9

I placed the chart over the range D12:M25

Try:

Sub CreateChartXY()
Dim ws As Worksheet
Dim chto As ChartObject, cht As Chart
Dim rChart As Range

Set ws = Worksheets("Sheet1")

' add the chart
Set rChart = ws.Range("D12:M25") ' range that the chart overlaps
With rChart
    Set chto = ws.ChartObjects.Add(.Left, .Top, .Width, .Height)
End With
chto.Name = "MyChart"
Set cht = chto.Chart

' adds the series
With cht

    ' set the chart type
    .ChartType = xlXYScatter

    ' add the first series
    
    With .SeriesCollection.NewSeries
        .Name = "Series 1"
        .XValues = ws.Range("XValues1")
        .Values = ws.Range("YValues1")
    End With

    ' add the second series
    
    With .SeriesCollection.NewSeries
        .Name = "Series 2"
        .XValues = ws.Range("XValues2")
        .Values = ws.Range("YValues2")
    End With

End With

End Sub