Redirecting linked named ranges

[DuckBill]

I have a spreadsheet which is well-used within my company, and I need to get users to add in a new reporting sheet. This is fairly straightforward using Edit | Move or Copy Sheet… from a new master file that they can access. There are links to be re-directed from the Master sheet to their file but thats fairly easy for them too.

What causes them some problems is that there are lots of named ranges used in the original spreadsheet which the new reporting sheet links into. But when the new sheet is copied into their workbook, the ranges are unique to the new sheet (i.e. in the Define Name list, they have the name of the new sheet on the right) and in most cases, the cells referenced are incorrect. (This is because although the same name exists in both their spreadsheet and the master, the ranges refer to different cells because they have expanded their spreadsheet in the course of their work). The only way that I know of correcting this is to have them go down the Define Name list and delete all the offending names (which then leaves the same name on the list, but no longer unique to the new sheet). Some users find this difficult.

Does anyone know a better way of deleting the "unique" names en masse. Perhaps a macro that I could create for them to use?

Thanks in advance,

DuckBill

 

[acron]

Make a copy of the sheet that the users copy from the master file, but remove the range names. Save the sheet as file and include a button/option to insert ta copy of the saved file as a new sheet in the workbook. The code to that is simply :

Sheets.Add Type:="Path\FileName", where path & filename is the saved sheet.

A.C.

 

[DuckBill]

acron,

Thanks for the idea. I saved the sheet as a file and removed the range names. But when the sheet is added into the workbook all the affected cells still say #NAME? and need to be refreshed (using F2 then Enter). I guess there is a way to do this in code...can you help?

DuckBill