Automating charts with 1 x-axis and multiple y-axes in VBA

Melissa2305 · Aug 26th 2018

I'm trying to automate the creation of graphs in Excel by means of a macro. I have lots of data (currently, columns A to AM). I want one graph in total, with multiple series.

I now have code for two series, but am wondering if it is possible to make a sort of loop that loops through the data and adds a series to the chart. I hope someone can help me in the right direction.

I currently have this:

Code

Sub AutomatedGraph()
'




'
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
    ActiveSheet.Shapes("Chart 2").IncrementLeft 202.5
    ActiveSheet.Shapes("Chart 2").IncrementTop -163.9285826772
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).XValues = "=Data!$A$24:$A$31"
    ActiveChart.FullSeriesCollection(1).Values = "=Data!$Y$24:$Y$31"
    ActiveChart.FullSeriesCollection(1).Name = "=Data!$Y$23"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).XValues = "=Data!$A$24:$A$31"
    ActiveChart.FullSeriesCollection(2).Values = "=Data!$Z$24:$Z$31"
    ActiveChart.FullSeriesCollection(2).Name = "=Data!$Z$23"


End Sub

Display More

JonathanVH · Aug 27th 2018

Something like this?

Code

Sub AutomatedGraph()
Dim StartRng As Range, xRng As Range, n As Long
  ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
  ActiveSheet.Shapes("Chart 2").IncrementLeft 202.5
  ActiveSheet.Shapes("Chart 2").IncrementTop -163.9285826772
  With Worksheets("Data")
    Set xRng = .Range("$A$24:$A$31")
    Set StartRng = .Range("$Y$24:$Y$31")
  End With
  With ActiveChart
    For n = 0 To 14
      .SeriesCollection.NewSeries
      With .FullSeriesCollection(n + 1)
        .XValues = "=Data!" & xRng.Address
        .Values = "=Data!" & StartRng.Offset(, n).Address
        .Name = "=Data!" & StartRng(0, n + 1).Address
      End With
    Next
  End With
End Sub

Display More

Melissa2305 · Aug 28th 2018

That seems to do it! Thank you!

JonathanVH · Aug 28th 2018

You're welcome.

It is annoying, though, that VBA code like this is sometimes necessary. You should be able to just select the X range and then, holding down the Ctrl key, add the data range. Then use the wizard and choose the chart type. The wizard, though, as is their wont, will be capricious. Sometimes it correctly creates the chart from the range, but sometimes it won't. If you get it working and then record a macro to get the code, the code often won't work the same way. This is why I avoid creating all but the simplest charts from VBA. Jon Peltier is the human wizard of this realm.

Automating charts with 1 x-axis and multiple y-axes in VBA

Participate now!

Share