Cannot change title of second chart

Sub Macro5()
'
' Macro5 Macro
' Macro does not change title of second chart
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)) 'eg: B44
NameLoc = "B" + RowLoc
Range(NameLoc).Select 'state name, eg: ALABAMA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1
Range(TextSheetColumn).Select
ActiveSheet.ChartObjects("Chart 1").Activate
With Worksheets("Charts").ChartObjects("Chart 1").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
ActiveWindow.Visible = False
Selection.Name = "Chart 2"
' Now have copy with known chart name.. so try implicit loop
ChartNum = 2
Sheets("#of wells").Select
RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B45
NameLoc = "B" + RowLoc
Range(NameLoc).Select 'state name, eg: ARIZONA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22
Range(TextSheetColumn).Select
ChartName = "Chart" + Str$(ChartNum)
ActiveSheet.ChartObjects(ChartName).Activate
With Worksheets("Charts").ChartObjects(ChartName).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
End With
' ChartName and CTitle are correct
' Title is NOT selected at this point and is NOT changed
End Sub

There is almost never a need to select or activate objects. Also, it is very
risky to rely on something being the active object. The code below does what
you want and can be run irrespective of what workbook/sheet is the active
element...as long as the target workbook is open and the appropriate
worksheets exist in it.

Option Explicit

Sub noSelectsOrActivates()
Const TitleName As String = "Stripper Well Survey - "
Dim aWB As Workbook
Set aWB = Workbooks("StripperWellsMod.xls")
'Set aWB = Workbooks("Book3")
Dim SheetColumn As Integer, ChartNum As Integer
SheetColumn = 1
ChartNum = 1
Dim CTitle As String
CTitle = TitleName & aWB.Sheets("#of wells").Cells(43 + ChartNum,
2).Value
With aWB.Worksheets("Charts").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With

'The variable SheetColumn is apparently incorrectly named _
since it is used to reference a row!

ChartNum = 2
With aWB.Worksheets("Charts")
.Paste
With .ChartObjects(.ChartObjects.Count)
.Top = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Top
.Left = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Left
With .Chart
.HasTitle = True
.ChartTitle.Text = TitleName _
& aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value
End With
End With
End With
End Sub

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Robert Baer" wrote:

Sub Macro5()
'
' Macro5 Macro
' Macro does not change title of second chart
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)) 'eg: B44
NameLoc = "B" + RowLoc
Range(NameLoc).Select 'state name, eg: ALABAMA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1
Range(TextSheetColumn).Select
ActiveSheet.ChartObjects("Chart 1").Activate
With Worksheets("Charts").ChartObjects("Chart 1").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
ActiveWindow.Visible = False
Selection.Name = "Chart 2"
' Now have copy with known chart name.. so try implicit loop
ChartNum = 2
Sheets("#of wells").Select
RowLoc = LTrim(Str$(43 + ChartNum)) 'eg: B45
NameLoc = "B" + RowLoc
Range(NameLoc).Select 'state name, eg: ARIZONA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22
Range(TextSheetColumn).Select
ChartName = "Chart" + Str$(ChartNum)
ActiveSheet.ChartObjects(ChartName).Activate
With Worksheets("Charts").ChartObjects(ChartName).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
End With
' ChartName and CTitle are correct
' Title is NOT selected at this point and is NOT changed
End Sub

Tushar Mehta wrote:

There is almost never a need to select or activate objects. Also, it is very
risky to rely on something being the active object. The code below does what
you want and can be run irrespective of what workbook/sheet is the active
element...as long as the target workbook is open and the appropriate
worksheets exist in it.

Option Explicit

Sub noSelectsOrActivates()
Const TitleName As String = "Stripper Well Survey - "
Dim aWB As Workbook
Set aWB = Workbooks("StripperWellsMod.xls")
'Set aWB = Workbooks("Book3")
Dim SheetColumn As Integer, ChartNum As Integer
SheetColumn = 1
ChartNum = 1
Dim CTitle As String
CTitle = TitleName & aWB.Sheets("#of wells").Cells(43 + ChartNum,
2).Value
With aWB.Worksheets("Charts").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With

'The variable SheetColumn is apparently incorrectly named _
since it is used to reference a row!

ChartNum = 2
With aWB.Worksheets("Charts")
.Paste
With .ChartObjects(.ChartObjects.Count)
.Top = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Top
.Left = aWB.Worksheets("Charts").Cells(SheetColumn + 21, 1).Left
With .Chart
.HasTitle = True
.ChartTitle.Text = TitleName _
& aWB.Sheets("#of wells").Cells(43 + ChartNum, 2).Value
End With
End With
End With
End Sub

Thanks, will give that a try.