CODEWITHSUNDEEP
excelvba
Iftikhar
Iftikhar

Reputation: 667

Loop through multiple sheets and make embedded graphs

I am doing the graphs as described in the following post Loop through sheets creating graphs But i face the problem while using the code below

Option Explicit
Public Sub graph()
    Dim ws As Worksheet
    Dim chrt As Chart
    Dim StartCell As Range
    For Each ws In Worksheets
        Set StartCell = ws.Range("e1")
        Set chrt = ws.Shapes.AddChart.Chart

        With chrt
            .SetSourceData Source:=ws.Range("$A$1:$A$45, $D$1:$D$45, $E$1:$E$45")
            .ChartType = xlLine

            .SeriesCollection(1).Name = ws.Range("$D$1")
            .SeriesCollection(1).XValues = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
            .SeriesCollection(1).Values = ws.Range("D2:D" & ws.Range("D" & ws.Rows.Count).End(xlUp).Row)
            .SeriesCollection(2).Name = ws.Range("$E$1")
            .SeriesCollection(2).XValues = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
            .SeriesCollection(2).Values = ws.Range("E2:E" & ws.Range("E" & ws.Rows.Count).End(xlUp).Row)

            '.HasTitle = True
            '.ChartTitle.Characters.Text = "Effektivitet"
        End With
    Next ws End Sub

It makes line chart but with the double line/series of E.

Upvotes: 0

Views: 30

Answers (1)

Iftikhar
Iftikhar

Reputation: 667

It has been solved. I am making mistake here

    With chrt
        .SetSourceData Source:=ws.Range("$C$1:$D$11")
        .ChartType = xlLine

Upvotes: 1

Related Questions