Some automated graphing

May 5, 2008

Heres some code I’ve used in one form or another to add charts for multiple worksheets that have the same layout (like monthly breakdowns of data)  

For Each wSheet In Sheets

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=wSheet.Range(”B4:AX32″), PlotBy:=xlRows
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=  ”Graph_” & wSheet.Name
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlRight
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.Deselect
    ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = “Arial”
        .FontStyle = “Regular”
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
   
    Next wSheet

Posted in Excel