您需要定义这些 Range 和 Shape 对象在哪个工作表中.例如

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

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

将 ws 定义为工作表:

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

请注意，如果语句位于 With 语句中，则它们只能以点号 .Range 开头.因此，上述内容与以下内容相同:

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

您还需要检查其他范围对象，因为它们的起始位置类似于 .Range("$ F $ 1")，它们引用的是 With chrt ，它是一个图表对象，但是他们应该参考 ws.Range("$ F $ 1").

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