Get Macro on one Worksheet to Work on Others

[Tom Dunlap]

The following macro to makes a chart from Sheet foo in a workbook that has 20 Worksheets. Each worksheet is identical with regard to the data, hence the Range command always works.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("foo").Range("B3:B508,E3:E508"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="foo"
ActiveChart.HasLegend = False

What needs to be done to make this work on all the worksheets in the Workbook.

 

[Tom Ogilvy]

If the sheet to be worked on is the activesheet
Dim sName as String
Dim sh as Worksheet
sName = Activesheet.Name
set sh = activesheet
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=sh.Range("B3:B508,E3:E508"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False

--
Regards

The following macro to makes a chart from Sheet foo in a workbook that has

20 Worksheets. Each worksheet is identical with regard to the data, hence
the Range command always works.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData

Source:=Sheets("foo").Range("B3:B508,E3:E508"), _