Plot many sheets in scatter chart

[diegomucino]

What about having one thousend or one hundred of sheets is there any solution to add them more quickly instead of writting every one on this code?



Sub MakeChart()
Dim rX1 As Range, rY1 As Range, rX2 As Range, rY2 As Range, rX3 As Range, rY3 As Range
Dim rChartPos As Range
Dim chtO As ChartObject

Set rX1 = Worksheets("final1").Range("F2:F2501"): Set rY1 = Worksheets("final1").Range("G2:G2501")
Set rX2 = Worksheets("final2").Range("F2:F2501"): Set rY2 = Worksheets("final2").Range("G2:G2501")
Set rX3 = Worksheets("final3").Range("F2:F2501"): Set rY3 = Worksheets("final3").Range("G2:G2501")

Set rChartPos = Worksheets("Resultados").Range("U5:Z10") ' location and dimensions of the chart
With rChartPos
Set chtO = .Parent.ChartObjects.Add(.Left, .Top, .Width, .Height)
chtO.Name = "MyChart"
End With

With chtO.Chart

.ChartType = xlXYScatterLines

' add first series
With .SeriesCollection.NewSeries
.XValues = rX1
.Values = rY1
.Name = "S1"
End With

' add second series
With .SeriesCollection.NewSeries
.XValues = rX2
.Values = rY2
.Name = "S2"
End With

' add third series
With .SeriesCollection.NewSeries
.XValues = rX3
.Values = rY3
.Name = "S3"
End With

.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = "My Chart Title"

End With


End Sub

 

[BSALV]

something like this (didn't test it)

for i=1 to 100
' add first series
With .SeriesCollection.NewSeries
.XValues = "='final" & i & "'!F2:F2501"
.Values = "='final" & i & "'!g2:g2501"
.Name = "S" & i
End With
next

 

[Jon Peltier]

I think BSALV's routine looks good (I didn't test it either).

Just so you know, you're limited to 255 series in an Excel chart. But if you approach this limit, you probably won't be able to read anything in the chart.