eliminating sheet references in a scatter plot

**johnnymac** · 02-15-2013, 08:15 PM

I have a scatter plot that I would like to copy to multiple pages with different data. Excel 2007 gives me an error if I try to use local ranges (such as A3:A55) in the "Select data" dialog. If I use "Sheet2!A3:A55" it is OK, but then I can not successfully copy that to a new worksheet without manually changing all the data references.

Since I want about 70 sheets in all, I'd like to be able to do this the easy way - copy a graph onto a page and have it use the local values.

Thanks for any hints you can give me.

John

**mrice** · 02-16-2013, 09:39 AM

You could try using a macro to reset all the ranges after you have copied. Something like below setting the ranges as appropriate

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**johnnymac** · 02-19-2013, 05:55 PM

I realize that this may be the only solution as charts seem to have no starting cell reference from which to generate multiple charts. I try to avoid macros because Excel seems to want to bombard the user more and more with security warnings. Most of my users will see those and many will not have macros enabled.