help on vba macro to put multiple charts on multiple worksheets based on table in each worksheet

[steve]

I have a 26 workbooks with about 100 worksheets for tracking stocks.
It is in the form of a table. It has date, open, low, high, close
volume, and adjusted close for the headers. Each worksheet is named
after the stock ticker name such as ATT. I am trying to create a macro
which will go through each sheet from a list in column C of a sheet
called Insurt and create 3 charts on each sheet. One with the prices
and dates excluding the volume for the entire table, approximately
1500 rows. The second chart is for 250 rows, approximately 1 year, and
the third chart is for 50 rows, approximately 3 months. I have a
couple of problems with the coding. 1) when I put A:A, G:G to set the
range for the entire table, it does not accept it. 2) When I use a
variable for the sheet name in the SetSourceDataSource instead of
'name', it errors.

Any help would be appreciated!!
Below is the coding I am using. I know it is ametuerish, but it's the
best I can do with the knowledge I have at this time.

Sub ChartMacro3()
Dim sheetname As String
Do While IsEmpty(Range("C1")) = False
Windows("StockTracker-C.xlsm").Activate
Sheets("Sheet1").Select
sheetname = Range("C1")
Sheets(sheetname).Select
Range("A1").Select
'ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$996"), ,
xlYes).Name = _
"Table2"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$2000"), ,
xlYes).Name = _
"Table"
Range("A:A,G:G").Select
ActiveSheet.Shapes.AddChart.Select
'ActiveChart.SetSourceData Source:=Range("'CAF'!$A:$A,'CAF'!$G:$G")
' ERROR BELOW WITH WORKSHEET VARIABLE!!!!!!
ActiveChart.SetSourceData Source:=Sheets(1).Range("$A$1:$G$2000")
'ActiveChart.SetSourceData
Source:=Range("'worksheet'!$A:$A,'worksheet'!$G:$G")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
'ActiveSheet.ChartObjects("Chart 1").Activate
Worksheets(1).ChartObjects(1).Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(5).Delete

Sheets(sheetname).Select
Range("A1").Select

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$250") _
, , xlYes).Name = "Table"
Range("Table1[#All]").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("worksheet!$A$1:$G250")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(5).Delete

Sheets(sheetname).Select
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$50") _
, , xlYes).Name = "Table"
Range("Table1[#All]").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("worksheet!$A$1:$G$50")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(5).Delete
Windows("StockTracker-C.xlsm").Activate
Sheets("Sheet1").Select
Range("C1").Select
Selection.Delete Shift:=xlUp

Loop
End Sub