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
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