Excel VBA: mirroring a chart

[bltseattle]

I am somewhat new to VBA and feel that this should be simple, but I'm stumped. I want a macro that will copy a chart from sheet A to sheet B, then resize the chart. Essentially mirroring the chart from A to B.

The source data is the same each time I use the sheet (or else this would be a no-brainer), and the original chart is prepared by pasting the data series (depending on which one the user picks) into the formatted chart area.

The macro works fine to copy and paste the chart, but it crashes on the resizing part. The problem seems to be that the name of the chart is different each time, so if it is "Chart 9" when I recorded the macro it is some different number such as "Chart 11" after I've performed the procedure several times. This crashes the macro.

What code would let me copy, paste, then select the new chart? (I can get the resizing part of the code from my recorded macro, if it would only pick the correct chart!)

 

[bltseattle]

Regarding my previous post, I meant to say that the source data is NOT the same each time. The chart is created using 3 of 12 different data sets which are selected custom by the user depending on the particular project the spreadsheet is being used on. And that is why the mirror of the source chart can not be linked to an absolute data source.

Sorry for not explaining this in the first post. Any help would be appreciated.


Brian