Format Chart w/ VBA Problem: Chart name changes w/ each new chart

[squidmark]

I have a macro that compiles data and creates a chart. However, I can't get the chart to format in the same macro. The problem seems to be the chart is given a new name each time the macro creates a new chart, and the macro is looking for a specific chart name, and the name it's looking for DOESN'T change.

So I get this Run-time error: "The item with the specified name wasn't found."

The Macro code the error references is:

ActiveSheet.Shapes("Chart 7").ScaleWidth 1.809895888, msoFalse, _
msoScaleFromBottomRight

Is there a way to assign the chart, each time it is created, the same name every time? And to make sure it's being referenced in the creation and formatting of the chart? There will never be more than one chart in existence -- it starts with a blank worksheet each time.

By the way, the macro was created using he VBA recorder. I can cut and paste and manipulate VBA code a bit, but am just barely learning to write with it, bit by bit.

thanks.

 

[daverunt]

Copy the recorded code here so everyone can see it and possibly help.

 

[squidmark]

Here is the VBA code for the creation of the chart

Range("B2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine

ActiveChart.SetSourceData Source:=Range("M:M,O:O,Q:Q,S:S,U:U,W:W")
ActiveChart.Legend.Select
Selection.Left = 69.569
Selection.Top = 18.875


With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.0500000007
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).AxisGroup = 2
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetElement (msoElementSecondaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = "Total Hours"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Total Hours"
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 11
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.PlotArea.Select
Selection.Width = 238.222
Selection.Width = 204.222
Selection.Left = 99.845
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Dollars"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Dollars"
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 11
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select


Thanks!