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 [...]

Posted in Excel No Comments »