Moving sheets between workbooks with the same named ranges

[Kelvin Stott]

I have two workbooks, each containing a similar "Reference" sheet of global constants, which are defined in about 100 named ranges. The named ranges are the same in each workbook and are scoped at workbook level so they can be used by other sheets in each workbook, but the global constants themselves are different in each workbook.

I want to be able to move or copy over one or more of the other sheets that refer to the named ranges from one workbook to the other, so that the sheets automatically link to the named ranges in the destination file rather than the source file, but I have two problems:

1. When I try to move or copy over multiple sheets, a dialog box opens hundreds of times to ask which source of named range I want to use for each every named range, for each and every sheet I move.

2. When I try to move or copy over only one sheet, Excel automatically retains a link to the named ranges in the original source file and ignores the named ranges in the destination file, which is not what I want.

So my question: Is there any way (e.g., changing the default settings in Excel, using some kind of event-driven macro, or anything else) to make sure that named ranges in the destination file are automatically used whenever moving or copying over single or multiple sheets from one workbook to another, without dialog boxes, etc.?

I want to make the process of moving and copying over sheets from one workbook to the other as simple and robust as possible for the end user.

Thanks for any help.

 

[Kelvin Stott]

Did I post in the wrong time zone when everyone is asleep?