unable to get the chartobjects property of the worksheet class

[rita]

Hi everyone,

I am very new to EXCEL, please refer to the macro and help
if you can.

My question is I got an error message "unable to get the
chartobjects property of the worksheet class" at the codes
( ActiveSheet.ChartObjects("Chart 3").Activate).

This recorded macro is just select a range then make a
chart out of it. Then cut and paste the chart to another
area on the same sheet. I think the error message came
when I select and cut the graph.

I stuck at I don't know how to get the program return the
chartobject property so that I can assign the name to
chartobject.

-----------------

Range("A115").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets
("Query4").Range("A115")
ActiveChart.Location Where:=xlLocationAsObject,
Name:="Query4"
ActiveWindow.Visible = False
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
'error=> ActiveSheet.ChartObjects("Chart 3").Activate
Range("G2").Select
ActiveChart.Paste
ActiveWindow.Visible = False
Windows("macrotesting2.xls").Activate
Range("A115").Select
Selection.Cut
Range("H23").Select
ActiveSheet.Paste
Range("L24").Select

 

[Jon Peltier]

Hi Rita -

The problem with a recorded macro is that it does exactly what you told
it to do the first time, then you need to make adjustments for future
use. Perhaps you no longer have "Chart 3", or you're using the macro on
a sheet without as many charts.

If it's the same chart as you created earlier in the code, you could
instead use a sequence like this, which uses a chartobject variable, and
creates the chart directly in the worksheet. You can then use the
variable later to refer to the same chart, without worrying how Excel
may have named it.

Sub AddChartObject()
Dim myChtObj As ChartObject
'
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.ChartType = xlXYScatterLines
myChtObj.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
End Sub

This is an excerpt from my page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon