Forum Discussion
Duplicated named ranges on sheet copy
- So solving this was relatively simple; rather than copying the sheet I copied the usedrange.
formats/formulas/tables etc all copy corretly but excel doesnt copy the named ranges.
Hopefully this is useful to someone else, as it seems to be a common query
I had a similar problem with a very similar use case: I built a costing workbook with several source sheets for standards and prices, featuring global named ranges, and then over a hundred standard template costing sheets that looked these up to build individual costings.
All the named ranges were global, but I noticed when copying any of the costing sheets, there was one named range for which Excel made a duplicate sheet-level range, every time. Since none of the other ranges did this, I looked a little deeper at what was different about this one.
Turns out, it was the only range that referenced a table range. All the others referenced a simple absolute range. I changed the Table range to a simple absolute range, and the behaviour stopped.
No idea why this should make a difference, but there you go!