遍历图纸创建图形 [英] Loop through sheets creating graphs

问题描述

我正在设置一个需要每周更新的工作簿.它包含10张纸，我试图创建一个为每张纸制作图形的循环.

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.

我尝试了一个带有sh.activate的循环，最终用所有工作表中的worksheet1中的数据创建了一个图形.在此之后，我尝试创建一个不带.activate的外观，尽管这样做确实能完成工作这是正确的方法吗?

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

推荐答案

您需要定义这些 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

这篇关于遍历图纸创建图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！