Thread: Duplicating Named Ranges

Afternoon all,

I'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it.

The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names.

Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?

I can attach what I have so far if needed but the file is 2.64mb. I didn't want to glog anything up with such a large sample file.

Thanks

Just copy the sheet.

If the names on the orginal sheet have workbook scope, the names on the copied sheet will have worksheet scope. Make an extra copy and delete the original, so they all have worksheet scope.

Originally Posted by shg

Just copy the sheet.

If the names on the orginal sheet have workbook scope, the names on the copied sheet will have worksheet scope. Make an extra copy and delete the original, so they all have worksheet scope.

SHG

Are you saying delete the original sheet containing the named ranges with workbook scope after copying the sheet? Then use the sheet with named ranges as scope worksheet to do the remaining copies?

The order doesn't matter. You can make multiple copies of the original, or copy the copies.

The point is just so that at the end, all the names have sheet scope.

Thanks for the help SHG. Now that I've been through it, it makes sense. I never tried to perform this kind of copy before. Thanks for the help.The only issue now is that 2.65mb file went to 24.2mb and it doesn't have any data or charts in it yet, but I guess that's a seperate issue.......

Maybe scrub the original before making copies -- delete unused rows and columns, clear unnecessary formatting, save, close, and reopen.

Grouped all the sheets and scrubed as is. Cut it down to a manageable 5mb. I quess I really didn't have to prep the sheet to hold quite so many years worth of data. Like everything else, it will probably be obsolete long before that time comes.

Thanks again. HAVE A GREAT WEEKEND!