Automate chart creation

[andre5]

Hi

I have a workbook with multiple sheets. I need to create the same type of chart on each sheet.
IE. I need a line chart with 3 lines. They are always in the same area but the sheet changes. series1 B6:b9 name1 b5, series2 D6:D9 name D5, series3 E6:E9 name3 E6, Category (X) axis lables are A6:A9 Location at row 13.

My bigest problem is that I do not have the sheet name which would change sheet to sheet.

How do I modify the following so that I could use it on any sheet?


Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Smooth Lines"
ActiveChart.SetSourceData Source:=Sheets("Sheet165").Range("D18")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet165!R6C1:R9C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet165!R6C2:R9C2"
ActiveChart.SeriesCollection(1).Name = "=Sheet165!R5C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet165!R6C4:R9C4"
ActiveChart.SeriesCollection(2).Name = "=Sheet165!R5C4"
ActiveChart.SeriesCollection(3).Values = "=Sheet165!R6C5:R9C5"
ActiveChart.SeriesCollection(3).Name = "=Sheet165!R5C5"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet165"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
ActiveSheet.Shapes("Chart 1").IncrementLeft -116.25
ActiveSheet.Shapes("Chart 1").IncrementTop 111#

 

[andre5]

Got it working Thank you for all your help:

working code:

StrName = ActiveSheet.Name

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Smooth Lines"
ActiveChart.SetSourceData Source:=Sheets(StrName).Range("D18")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=" & StrName & "!R6C1:R9C1"
ActiveChart.SeriesCollection(1).Values = "=" & StrName & "!R6C2:R9C2"
ActiveChart.SeriesCollection(1).Name = "=" & StrName & "!R5C2"
ActiveChart.SeriesCollection(2).Values = "=" & StrName & "!R6C4:R9C4"
ActiveChart.SeriesCollection(2).Name = "=" & StrName & "!R5C4"
ActiveChart.SeriesCollection(3).Values = "=" & StrName & "!R6C5:R9C5"
ActiveChart.SeriesCollection(3).Name = "=" & StrName & "!R5C5"
ActiveChart.Location Where:=xlLocationAsObject, Name:=StrName
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
ActiveSheet.Shapes("Chart 1").IncrementLeft -116.25
ActiveSheet.Shapes("Chart 1").IncrementTop 111#