CODEWITHSUNDEEP
vbaexcelcharts
Jelle Postma
Jelle Postma

Reputation: 15

create chart with data from multiple sheets using VBA

I'm simply trying to: create a chart with data from multiple sheets using VBA.

Something strange happens in my for loop where at the end every series has the same data applied. (instead of every series showing data from the corresponding sheet, who are all different)

using this code:

Private Sub CommandButton1_Click()

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="packetLoss"

For i = 1 To 8
    ActiveChart.SeriesCollection.NewSeries
Next i

For i = 1 To 8
    Dim chartName As String
    chartName = "packetsOverTime" & (i + 3)
    Set xRng = Sheets(chartName).Range("B2:B1000")
    ActiveChart.SeriesCollection(i).Values = Sheets(chartName).Range("B2:B1000")
    ActiveChart.SeriesCollection(i).Name = chartName

Next i

End Sub

while this code correctly displays the data of "packetsOverTime5" 

Private Sub CommandButton1_Click()

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="packetLoss"

For i = 1 To 8
    ActiveChart.SeriesCollection.NewSeries
Next i

'this is new: just apply data from "packetsOverTime5"
chartName = "packetsOverTime5"
ActiveChart.SeriesCollection(i).Values = Sheets(chartName).Range("B2:B1000")
ActiveChart.SeriesCollection(i).Name = chartName

End Sub

And this last piece of code has the same result as the first piece:

Private Sub CommandButton1_Click()

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="packetLoss"

For i = 1 To 8
    ActiveChart.SeriesCollection.NewSeries
Next i

'this piece is back:
For i = 1 To 8
    Dim chartName As String
    chartName = "packetsOverTime" & (i + 3)
    Set xRng = Sheets(chartName).Range("B2:B1000")
    ActiveChart.SeriesCollection(i).Values = Sheets(chartName).Range("B2:B1000")
    ActiveChart.SeriesCollection(i).Name = chartName

Next i

'this piece doesn't have any effect now
chartName = "packetsOverTime5"
ActiveChart.SeriesCollection(i).Values = Sheets(chartName).Range("B2:B1000")
ActiveChart.SeriesCollection(i).Name = chartName

End Sub

I have programming experience of over 4 years (never with VBA or/in excel tho), but I don't understand whats causing this problem.

If you have any clue please let me know :)

Thanks in advance, Jelle

Upvotes: 0

Views: 5066

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

This worked for me (slightly different setup in my case)

Private Sub Tester()

Dim cht As Chart, s As Series, xRng As Range
Dim i As Long, chartName As String

    Set cht = Charts.Add
    cht.ChartType = xlLine
    cht.Location Where:=xlLocationAsNewSheet, Name:="packetLoss"

    For i = 1 To 3

        chartName = "Sheet" & i
        Set xRng = Sheets(chartName).Range("A1:A10")

        With cht.SeriesCollection.NewSeries()
            .Values = xRng
            .Name = chartName
        End With

    Next i

End Sub

Upvotes: 1

Related Questions