Excelでグラフを一括生成するスクリプト


多くのデータに基づいて様々なバグ曲線のレポートを生成することが多いので、時間を割いてこのスクリプトを完成し、必要なものは自分で修正することができます.
Function getcolname(ByVal intcol As Long)
    intcol = intcol - 1
    getcolname = IIf(intcol \ 26, Chr(64 + intcol \ 26), "") & Chr(65 + intcol Mod 26)
End Function

Sub  ()
'
'  
' by marty
'  sheet1 

    x = 0
    y = 0
    Sheets.Add After:=Sheets(Sheets.Count)
    'ActiveSheet.Name = "Bug "
    ' sheet1 
    intcol = Sheets("Sheet1").Range("IV1").End(xlToLeft).Column
    strCol = getcolname(intcol)
    ' 
    For i = 2 To 6
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C" & i & ":" & strCol & i)
        ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & strCol & "$1"
        ActiveChart.SeriesCollection(1).Name = "=Sheet1!$B$" & i
        ' x , 
        ActiveChart.Axes(xlCategory).Select
        ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
    Next i
    ' 
    For Each Chart In ActiveSheet.ChartObjects
        Chart.Activate
        ActiveChart.ChartArea.Select
            Chart.Top = y * 206
            Chart.Left = x * 606
            Chart.Height = 200
            Chart.Width = 600
        x = x + 1
        If x >= 1 Then
            x = 0
            y = y + 1
        End If
    Next Chart
    
End Sub