I have some complicated macros which help create quotes for our sales department. I am using a "configurator" workbook which contains a few template sheets and over 50 ranges which point to various variables/templates. In the process of creating the quotes I end up opening/creating a new workbook and copying the template ranges/sheets to the new workbook (through the macro). This new "Quotes" workbook will contain a coversheet with many variables containing named ranges which subsequent sheets will need to reference with "=NAMED_RANGE" or something like that.

Right now excel is annoying me because when it copies references of named ranges from one workbook to another, the cell tries to refer back to the "configuration" workbook (although it doesn't say so). I want to be able to use something like "=ThisWorkbook!NAMED_RANGE" which would solve all my problems but it looks like MS isn't going to make it that easy for me (I can't find any info on a special reference like ThisWorkbook for formulas).

The only workaround I could think of would be to create a macro to scan through all the cells with references to named ranges in the new "Quote" workbook and insert the new workbook name in front of them (i.e. =quote1.xls!NAMED_RANGE). This would take forever and then I would have problems if the user renamed the workbook.

Anyone have any suggestions? Please I'm dying here.