I have a WB created in Excel 2003 which uses Named Ranges extensively. In
Excel 2003, if I use VBA to copy the WS that contains the named ranges from
one WB to another, the Named ranges become Workbook global to the target WB
(which is what I want). But in 2007, the Named Ranges remain Workbook Global
to the SOURCE workbook. For example, the Refers to in the Target WB are:
2003:
=Lists!$Y$7
2007
='D:\My Data\CDM\Budget\2007\templates\[CDM_Budget_20.xlt]Lists'!$Y$7
Does anyone have any idea how to work with this??? It's critical for my
design that I copy WS from one workbook to another that contain these lists.
My design is this:
I have a template that contains a WS 'Lists' that define a whole bunch of
Named Ranges used to create dropdown lists on users WSs for data entry. Users
create working WB from this template. To maintain the lists, and allow for
updates, the users can get an updated 'Lists' WS from the template. VBA code
copies the updated 'Lists' WS from the template to their working WBs.
In 2003 this worked GREAT. But in 2007, this breaks because the Named Ranges
in the copied WS continue to reference the template that is now closed.
Any help is greatly appreciated.
John
|
Similar Threads
