I have recently created a fancy spreadsheet in Excel (97), which pulls data from a standard table on one worksheet, and creates line/column graphs from that data in another.
I've put the workbook together with Macros, and there are macros that perform each step of the process, e.g. drawing the graph (using Wizard method), creating and formatting the title, etc. There is also one over-arching Macro that basically runs all the individual macros one after the other, allowing you to press one key and hey presto! Twenty new graphs....
All very impressive, but the computer draws all twenty graphs on top of one another on the same point in the worksheet, whereas clearly I would like the charts on separate pages (and without the hassle of having to move them around myself every month). But I can't figure out for the life of me how to do that.
If I write a Macro to move the chart (to a page of its own), it doesn't work the next time I create the charts, because the name or reference to the chart employed by Visual Basic behind the scenes (e.g. 'Chart 371') changes (it seems to be an arbitrary name, different every time you create a chart).
Is there some way of fixing the title of a chart, that would allow visual basic to recognise the chart object I want it to move, even when I've only just created that chart using the macros? Can I perhaps just instruct visual basic to move the 'Active' chart object, and avoid using it's arbitrary name at all?