Insert Sheet from Template / Named Range Conflict

Hello, everyone! Hope you're all doing well! It's been awhile since I've posted only because I couldn't solve this issue on my own with the help of F1 and Google.

I've got a workbook that I use to itemize and total the costs of different material assemblies. Each material assembly resides on its own worksheet within the workbook, and the workbook contains a summary sheet that totals up the costs from these many worksheets. The worksheets for the material assemblies are added from a template that has named ranges for cells containing subtotals with a scope of Workbook. The workbook to which they're being added has the same named ranges with the same scope so that all such added worksheets have their subtotals added up into a grand total on the summary sheet in the main workbook. However, when the new sheets are added from the template, their named ranges have their scope changed to Worksheet instead of Workbook and their subtotals are therefore never added to the summary sheet in the workbook. As usual, I'm looking for a solution that would preferably involve native worksheet functions or even changes to application settings I may be missing but I am amenable to VBA procedures if the consensus is that's the only option. Thanks to all for reading!

Hi,

I was doing some research on this earlier (for a similar reason) for someone I've been helping - I ended up recommending just manually re-defining the names as there were only 24 to be changed. If you've got a lot more to do, the only page I've found which has some code which might work for you is this one:
http://stackoverflow.com/questions/1...ed-range-scope
About 3/4 of the way down the page is a post which says this, 'Check out these two subs that reverse each other and flip the scope (worksheet to workbook or reverse) of all named ranges that refer to a range on the active sheet.' which sound like it's exactly what you want. However...please note that I have not tried any of the solutions so can't say if/how well they work, sorry!

Good luck.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

That's definitely very good information, Aardigspook, I appreciate you sharing that!