Sub Macro8()
'
' Macro8 Macro
' Macro does not change Series references
TitleName = "Stripper Well Survey - "
Windows("StripperWellsMod.xls").Activate
Sheets("Charts").Select
Range("A1").Select
SheetColumn = 1
ChartNum = 1
ActiveSheet.ChartObjects("Chart 1").Activate
Sheets("#of wells").Select
RowLoc = LTrim(Str$(43 + ChartNum))
NameLoc = "B" + RowLoc 'eg: B44
Range(NameLoc).Select 'state name, eg: ALABAMA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1
Range(TextSheetColumn).Select
ChartName = "Chart" + Str$(ChartNum) 'eg: Chart 1
ActiveSheet.ChartObjects(ChartName).Activate
With Worksheets("Charts").ChartObjects(ChartName).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
End With
' Title is selected at this point and is changed
ActiveChart.ChartArea.Copy
SheetColumn = SheetColumn + 21
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22
Range(TextSheetColumn).Select
ActiveSheet.Paste
' Now have two identical charts with second one selected
ChartNum = ChartNum + 1
ChartName = "Chart" + Str$(ChartNum) 'eg: Chart 2
ActiveWindow.Visible = False
Selection.Name = ChartName
' Now have copy .. so try changing..
RowLoc = LTrim(Str$(43 + ChartNum))
Series_1 = "='#of wells'!$C$" + RowLoc + ":$BQ$" + RowLoc
Series_2 = "=Production!$C$" + RowLoc + ":$BQ$" + RowLoc
' ActiveChart.SeriesCollection(1).Values = Series_1
' ActiveChart.SeriesCollection(2).Values = Series_2
' Following code stolen from macro recorded when changing Y Values
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "='#of wells'!R45C3:R45C69"
ActiveChart.SeriesCollection(2).Values = "=Production!R45C3:R45C69"
Windows("StripperWellsMod.xls").ScrollRow = 13
ActiveWindow.Visible = False
Windows("StripperWellsMod.xls").Activate
' Above code makes NO changes, so..
' eXplicit values from chart give error "unable to set the Values
property..
' same problem without the $ signs..
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "='#of wells'!$C$45:$BQ$45"
ActiveChart.SeriesCollection(2).Values = "=Production!$C$45:$BQ$45"
Windows("StripperWellsMod.xls").ScrollRow = 13
ActiveWindow.Visible = False
Windows("StripperWellsMod.xls").Activate
'
End Sub