I have a worksheet with about a dozen named cells/ranges and several formula that use those named cells.
I need to replicate that sheet several times in the same workbook. If I simply make a copy of the worksheet, all range names are converted to local scope. I have VBA code to rename the ranges on the copied sheets in order to make every name globally unique, however, I then need to change the scope to global on each sheet.
I've seen several solutions here for going the other direction -- from global to local -- that use VBA to delete and recreate each range name. I don't want to do that, however, because of the formulas that refer to each named range.
Is there a way to switch my names from local to global while preserving my formula? I think I can work around the problem by copying my worksheet to a new workbook to preserve the global scope, renaming ranges, and then copying back, but I would prefer a solution that works within the original workbook.