Excel inconsistency

R

Robert Baer

After a bunch of fiddling, i got a macro to duplicate a chart below a
single one on a worksheet.
But there was a runtime error as noted near the end of the macro.
So i did a lot of more fiddling and was able to get a number of
copies, one after the other by calling them all "Chart 1".
Nasty.
Did other fiddling in attempts to change series ranges and title on
the fly.
Things got worse, so i tried to go back to the original macro as seen
below.
Even deleting the XLSTART folder and re-trying the macro did no good.
**HELP**

Sub Macro2()
' Macro2 Macro
' Macro recorded 4/15/2009 by Robert Baer
Range("A1").Select
SheetColumn = 1
ActiveSheet.ChartObjects("Chart 1").Activate
'*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1"
For ChartNum = 1 To 20
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
'*NOW FAILS------------------------------------^
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
'*NOTE redundant and useless duplication below*
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(2).Points(67).Select
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Windows("StripperWells.xls").Activate
SheetColumn = SheetColumn + 21
TextSheetColumn = "A" + LTrim(Str$(SheetColumn))
ChartNum = ChartNum + 1
TextChartNum = "Chart" + Str$(ChartNum)
Range(TextSheetColumn).Select
ActiveSheet.Paste
ActiveSheet.ChartObjects(TextChartNum).Activate
' ** Runtime error 1004 - unable to get the ChartObjects property
' of the Worksheet class
' Note: second chart has been created where i wanted it, and
' it is selected.
'*That is to say, when it worked..i did a lot of mods and
' (as above) it is all messed up
Next ChartNum
End Sub
 
J

Jacob Skaria

Can you paste this in immediate window and check whether the name is "Chart
1" itself...

?ActiveSheet.ChartObjects(1).name
 
R

Robert Baer

Jacob said:
Can you paste this in immediate window and check whether the name is "Chart
1" itself...

?ActiveSheet.ChartObjects(1).name
What is an "immediate window", how can i find it?
How does one go about finding the name of a chart, changing it and
"resetting" that count (assuming ther is one)?
 
D

Dave Peterson

The immediate window is in the VBE.
Hit alt-f11 to enter the VBE (where macros live)
Hit ctrl-g to see the immediate window

You can also see the name of the chart by ctrl-clicking on its edge (in excel)
and looking at the namebox (to the left of the formulabar).

And after it's selected, you can also change the name by typing the new name in
that namebox and hitting enter.
 
R

Robert Baer

Dave said:
The immediate window is in the VBE.
Hit alt-f11 to enter the VBE (where macros live)
Hit ctrl-g to see the immediate window

You can also see the name of the chart by ctrl-clicking on its edge (in excel)
and looking at the namebox (to the left of the formulabar).

And after it's selected, you can also change the name by typing the new name in
that namebox and hitting enter.
Thanks!
Isn't it wonderful that there are all of these obscure, undocumented
tricks from M$?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top