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 Whe=xlLocationAsObject, Name:="foo"
ActiveChart.HasLegend = False

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

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 Whe=xlLocationAsObject, Name:=sName
ActiveChart.HasLegend = False

--
Regards

"Tom Dunlap" wrote in message
...
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 Whe=xlLocationAsObject, Name:="foo"
ActiveChart.HasLegend = False

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