OzGrid

How to automate charts with 1 x-axis and multiple y-axis in VBA

Requirement:

The user is trying to automate the creation of graphs in Excel by means of a macro. The user has lots of data (currently, columns A to AM). The user wants one graph in total, with multiple series.

The user now has 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.

The user currently has this:

 
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

Solution:

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

Obtained from the OzGrid Help Forum.

Solution provided by Melissa2305.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery