1. Home
  2. Electronics Design
  3. OT: Hexcel inconsistencies - help!

OT: Hexcel inconsistencies - help!

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

Reply to
Robert Baer
Loading thread data ...

Why don't you ask on microsoft.public.excel.charting where the question would be on topic and the experts reside?

Start by weeding out all the bogus no-ops that are in there. I presume you captured it as a recorder macro without making any attempt to minimise the number of clicks and keystrokes. Practice to find the minimum simplest sequence to get the job done - then record it.

Most of the .Selects are bogus because they are immediately overridden on the next line by another selection and nowhere is Selection. referred to or used.

Why did you even think it would?

ActiveChart.ChartArea.Copy Range("I36").Select ActiveSheet.Paste

Would appear to do what you want fairly painlessly. Change "I36" to wherever you want the new copy placed. It will automatically increment the Chart number.

You could also capture the VBA sequence for create a chart with specified data and then adjust it to do exactly what you want. I cannot really see the point in copying a chart and its data multiple times.

You should be able to capture such a macro yourself by selecting the chart area and then doing copy and paste manually.

What you do not want to do is start recording a macro click randomly all over the screen until you hit the thing you want and then save the macro. Make it as simple as possible for yourself first and then record a macro using the smallest number of clicks to get the job done.

Excel 2003 tends to produce verbose hardwired macro recordings but they will usually work. Excel 2007 rarely produces stable macros re

Mutter mutter. Vista just gratuitously shut down on me this morning to install *Urgent Security* patches. It had been nagging me every 5 minutes to reboot but I was busy. Then it just did it spontaneously.

Thank heavens for autosave.

Regards, Martin Brown

Reply to
Martin Brown

Thanks for the ideas. What i really want to do: Start with one chart that has the desired looks (2 variables, each with its own grids and colors. Copy to new position and change the title (part of name fixed + part of name in a cell on another sheet), as well as change both series to pick up the row-associated data (wells on one sheet, production on the other). Have a number of states and areas to generate, so would be nice to automate it. The nastiest part is to export each chart as a GIF or BMP with associated name. Yes, i found the hidden trick (ain't M$ wonderful?) to copy as picture, but ?paste in document? does not seem to hack it for exporting.

Reply to
Robert Baer

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.