Cannot change series..help!

[Robert Baer]

' Also, chart size slowly gets LARGER.

Sub Macro7()
'
' Macro7: Chart titles and positions change OK; cannot change series

TitleName = "Stripper Well Survey - "
Windows("StripperWellsMod.xls").Activate
Sheets("Charts").Select
Range("A1").Select
SheetRow = 1
ChartNum = 1
CTitle = TitleName + Sheets("#of wells").Cells(43 + ChartNum, 2).Value
' With Worksheets("Charts").ChartObjects(1).Chart <---does not work here
' so be eXplicit..
With Worksheets("Charts").ChartObjects("Chart 1").Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With
For ChartNum = 2 To 5
CTitle = TitleName + Sheets("#of wells").Cells(43 + ChartNum, 2).Value
RowLoc = LTrim(Str$(43 + ChartNum))
ChartName = "Chart" + Str$(ChartNum) 'eg: Chart 2
SheetRow = SheetRow + 21
' Series_1 = "='#of wells'!$C$" + RowLoc + ":$BQ$" + RowLoc
' Series_2 = "=Production!$C$" + RowLoc + ":$BQ$" + RowLoc
' Alternate eXplicit form for ChartNum=2
Series_1 = "='#of wells'!R45C3:R45C69"
Series_2 = "=Production!R45C3:R45C69"
With Worksheets("Charts")
.Paste
With .ChartObjects(.ChartObjects.Count)
.Top = Worksheets("Charts").Cells(SheetRow, 1).Top
.Left = Worksheets("Charts").Cells(SheetRow, 1).Left
' Following does not work; "Unable to set the Values property of the
Series class"
' Furthermore, it looks like a number of charts were made one on top of
' the other, and titles were rotated, charts looks squashed and
' Hexcel barfed, writing to "error log" which cannot be found.
' .Chart.SeriesCollection(1).Values = Series_1
With .Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With
End With
End With
' Following does not work; "Object doesn't support this property or method"
' Worksheets("Charts").SeriesCollection(1).Values = Series_1
Next ChartNum
' Following does not work; "Unable to set the Values property of the
Series class"
' With ActiveSheet.ChartObjects
' .Item(.Count).Chart.SeriesCollection(1).Values =
"=Production!$C$45:$BQ$45"
' End With

' deliberate error to force allowance of debug
ActiveChart.ChartTitle = "foo"
End Sub