I'm importing a sheet (template) from an external workbook via a VBA scripted macro. The sheet imports perfectly, but I am having troubles with the drop-down lists.

I have tried creating instances in the Name Manager to refer to the range of the drop-down boxes, using these names in the Data Validation - but when I import a new sheet I find that the Name Manager has essentially hard coded the range reference to the source file. I need it to refer to the local sheets

NameManager.JPG

In this example, I deleted the Name Manager instance of "Sizes", but left the range reference in the Data Validation. I found that when importing it made a local reference to the correct sheet & range, but the drop-down box was inactive. I could only activate the contents of the drop-down box by clicking Data Validation and then OK (which I presume refreshes the link?)

Is there a way that I can create the drop-down structure (with or without the use of Name Manager) that will reference data in the "Data" sheet locally within the source workbook; that will in turn reference the local sheet when imported into the target workbook?

Alternatively, is there a way to reference the "Data" sheet remotely in the source workbook without having to open that file?

I just need to maintain the integrity of the drop-down boxes after importing the sheet, I don't really care how I get to that solution!

Thank you