excel macro help: ripple through each worksheet and create a new chart

[LordSnailz]

I'm sure this is easy for you excel gurus -
-The worksheet is setup that each name of the worksheet were the data resides is in "1", "2", "3", etc.
-The data is on column A,B,D and E.
-The graph is an overlay xy scatter plot, between A and B and D and E.

Can the script go through each worksheet and create a chart and place it on a new sheet, called "Chart 1", "Chart 2", etc

Hope I'm making sense, any help is greatly appreciated!

 

[LordSnailz]

I dunno if this helps explain what I did but here's the recorded macro on what I want it to do.
For the code below, is there anyway to make the sheet name a variable and is there anyway to set the name of the chart to be 'Chart Sheetname'


Sheets("1").Select
Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("1").Columns("A:B")
ActiveChart.Location Where:=xlLocationAsObject, Name:="1"
ActiveWindow.Visible = False
Windows("a.xls").Activate
Columns("D:E").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
ActiveWindow.Visible = False
Windows("a.xls").Activate


Sheets("2").Select
Columns("A:B").Select
Application.CutCopyMode = False
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("2").Columns("A:B")
ActiveChart.Location Where:=xlLocationAsObject, Name:="2"
ActiveWindow.Visible = False
Windows("a.xls").Activate
Columns("D:E").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True