Coping chart in each worksheet and updating source data

I am analysing a huge database which is composed of 30 worksheets in around 30 excel files. For each worksheet there is a chart that needs to be drawn using the data. So, I created the first chart, and then I copy it to the
following worksheet, but unfortunately, the source data is kept the same.
I need the source data to update such that it uses the data from the new
worksheet the chart is copied in.

So far after reading and asking I reached to the following code:

I still need the macro to update the source for the active worksheet name. To be clearer, in the code, the sheet: Sheet1 is put into quotes so it is not updated to the next sheet name for every loop (Sheet2, sheet3, and so on.) These sheets are already created, they just need to be called and the source of the data of the chart needs to be updated for each worksheet! I hope it is clear now.

Thanks!


Sub MakeCharts()
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
Call chart
Next sht
End Sub
Sub chart()
'
' chart Macro
'
' Keyboard Shortcut: Ctrl+w
'
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("H11")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R18C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C2:R18C2"
ActiveChart.SeriesCollection(1).Name = "=""steering angle"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "steering angle"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Distance"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Steering
Angle"
End With
End Sub

See similar question, this may help you???

http://www.excelforum.com/showthread.php?t=578339

Thanks, but I dont want to change the source of the data manually since it will take days to do on 30 worksheets for 30 files. I wanted to do it using a macro.

Also, using the F9 key makes the chart use the numbers of the original sheet and doesnt update the source for the following worksheets.

I did read that thread before posting.

I hope there is another solution than manually changing the source of data for each worksheet.

Thanks!

Has anyone found a solution for my problem yet?

I tried to find a way out by writing Activesheet.name instead of "Sheet1".
It's still not working!
I've beenworking on this for 3 days, and I feel I can't reach a solution at all.

I thought you guys were experts.

Any suggestions.