Copying sheets containing named ranges with VBA (Quick Question)

[Will S]

This should be a fairly simple question, I've already got the code for copying sorted, I'm just curious about several of the named ranges within the sheet and what happens to them when I copy it all over.

I'm working from a spreadsheet made by my predecessor and they've got a template sheet with numerous named rages all scoped to the workbook, on the new template I've created, it has lots of ranges scoped to the sheet itself. The new template works just fine as it is but I just want to try and make sure that when I change over the templates it will continue to work fine and the current template's named ranges are going, so far I have actually copied everything and are running tests, but you can't test for everything, so does anyone know if the named ranges on the new template being only scoped to the sheet will affect it at all?

Best regards,
~Will S

 

[CharlesChuckieCharles]

a quick pointer

Scope is the key word, if other sheets need to reference the Named range for a sheet than they need different syntax =Sheet1!MyValueCell


whereas a workbook scoped range is =MyValueCell



You can have the same name for a sheet named range and a workbook named range at the same time but i would not advise it

 

[Will S]

Thanks for the reply and pointer.

I agree with having unique names, that's the principle I've worked on with my template, when this sheet is copied through, the names get copied and changed to link correctly, even if they are scoped to the original sheet?

E.G Sheet1NamedRange when copied goes to Sheet2NamedRange

As for having two identical names with different scopes, after messing around with this sheet, it seems that it will just ignore the "Workbook" name for the "worksheet" one, so having =MyValueCell will still work for a workbook range, so long as there is not one in the current worksheet with that name, then you need the Sheet1! address.

Eventually I'll be deleting the old template, so all the named ranges scoped to the workbook will be gone, just want to make sure I won't lose any functionality, so far the tests wouldn't suggest so, but just in case I'm asking to avoid looking at something small in a week from now that I'd miss and find it's "FUBAR".