CODEWITHSUNDEEP
excelvba
troels
troels

Reputation: 43

Loop through sheets creating graphs

Im setting up a workbook that needs weekly updates. It contains 10 sheets and im trying to create a loop that makes a graph for each sheet.

I tryed a loop with sh.activate, that ended up creating a graph with the data from worksheet1 in all the worksheets. After this i tried to create a look without .activate it doenst work though Is this the correct way to do it?

Sub graph()
    Dim ws As Worksheet
    Dim chrt As Chart
    Dim StartCell As Range

    For Each ws In Worksheets
        Set StartCell = .Range("e1")
        Set chrt = .Shapes.AddChart.Chart

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

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

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

Upvotes: 1

Views: 290

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

You need to define in which worksheet these Range and Shape objects are. Eg in

Set StartCell = .Range("e1")
Set chrt = .Shapes.AddChart.Chart

define ws as worksheet:

Set StartCell = ws.Range("e1")
Set chrt = ws.Shapes.AddChart.Chart

Note that statements can only begin with a dot .Range if they are within a With statement. So the above is the same as the following:

With ws
    Set StartCell = .Range("e1")
    Set chrt = .Shapes.AddChart.Chart
End With

You also need to check your other range objects because they start like .Range("$F$1") they refer to With chrt which is a chart object but they should refer to ws.Range("$F$1").

So you should end up with something like:

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("$C$1:$D$11")
            .ChartType = xlLine

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

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

Upvotes: 4

Related Questions