We help IT Professionals succeed at work.

Moving Excel charts with Macros: Visual Basic Problem

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?

Watch Question

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
What about this:

After the creation of the chart, locate it to the sheet you want:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

Author of the Year 2009

Hi CagedAdministrator,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept angelIII's comment(s) as an answer.

CagedAdministrator, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.

CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.