Question

I recorded a macro to create graphs on a worksheet. The data are organized in the same way in all the sheets of the workbook, therefore I would like to generalize the macro so that it can be used on every sheet (or if it is possible to batch through the worksheets).

守则认为:

ActiveWindow.SmallScroll Down:=-57
Range("C5:C65").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("fr_1!$C$5:$C$65")
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).XValues = "=fr_1!$A$5:$A$65"

在把宏观数据记录在fr_1上之后,我现在在第5行和最后一行都提到这一点,而我要笼统地提及积极的表格。

我如何这样做?

Answer 1

你可以:

Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
    With aSheet.Shapes.AddChart.Chart
        .ChartType = xlLine
        .SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C$5:$C$65")
        .SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A$5:$A$65"
    End With
Next

If you want to iterate the manually selected sheets change to for each asheet in activewindow.selectedsheets

a. 按姓名人工过滤;

Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
    select case aSheet.name
        case "sheet1", "sheet50", "sheet999"   
            With aSheet.Shapes.AddChart.Chart
                .ChartType = xlLine
                .SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C$5:$C$65")
                .SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A$5:$A$65"
            End With
    end select
Next

Answer 2

你们

add the desired sheets to an Array and just access the sheets you want to place charts on. This code only runs on the three sheet names supplied by Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space")
skip the potential naming issue by referring just to local ranges

[Updated ——对潜在无效姓名的加差处理]

    Sub Sample()
    Dim ws As Worksheet
    Dim Arrshts()
    Dim ArrSht
    Dim strOut As String
    Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space")
    For Each ArrSht In Arrshts
    On Error Resume Next
    Set ws = Nothing
    Set ws = Sheets(ArrSht)
    On Error GoTo 0
    If Not ws Is Nothing Then
        With Sheets(ArrSht).Shapes.AddChart.Chart
            .ChartType = xlLine
            .SetSourceData Range("$C$5:$C$65")
            .SeriesCollection(1).XValues = Range("$A$5:$A$65")
        End With
    Else
        strOut = strOut & (vbNewLine & ArrSht)
    End If
    Next
    If Len(strOut) > 0 Then MsgBox strOut, , "These array names are incorrect and need adjusting"
    End Sub

友情链接