Create a chart with data range not related to any specifiedworksheet.

[dobrzanski]

Hi All, I have a question how can I create a chart with a data range
that will not refer to any particular worksheet. So I can record macro
when inserting and configuring a chart, and then repeat it on another
worksheet by simply running the macro.
Currently I must record and manually edit the macro to remove all
references to "Sheet!" in ranges. Is that possible?
The point is to teach a non-technical person to work better by
recording and running macros... I'm using office 2003 but solution for
Office 2007 will be great!

Thanks,
Gborek

 

[IgorM]

Hi All, I have a question how can I create a chart with a data range
that will not refer to any particular worksheet. So I can record macro
when inserting and configuring a chart, and then repeat it on another
worksheet by simply running the macro.
Currently I must record and manually edit the macro to remove all
references to "Sheet!" in ranges. Is that possible?
The point is to teach a non-technical person to work better by
recording and running macros... I'm using office 2003 but solution for
Office 2007 will be great!

Thanks,
Gborek

Hi

I don't think it's possible. VBA always 'hard-codes' names. But you can
always edit the macro, so for instance instead of
Worksheets("Sheet1") you can use Worksheets(1) so the first sheet in
your workbook will be used.
Hope it helps.

 

[Gord Dibben]

Record once.

Change the hard-coded sheetname to ActiveSheet.

Now will operate on whichever sheet is active when you run the macro.


Gord Dibben MS Excel MVP