Thanks guys,
Using the code "LocationsDCWName = ThisWorkbook.Worksheets("DCW
reference data").Range("CoreLoc").Value", provided by yourselves, I
was able to get past Line 22. Incidentally just to let you know, I
turned off the interbal Error Handler in the code to see what error
Excel was throwing when I was getting stuck at line 22. This was
"Runtime error "1004': Method 'Range' of object '_Global' failed".

My script however now is failing at line 26 which is "Sheets
(glCoreLocSheet).Select". It is a simple selection of a worksheet in
the target Excel document (where I am importind data from).
glCoreLocSheet has been defined in the StartProgram Module and has the
value Core Locations.

Turning the Error Handling code off is giving me the following Excel
VBA error "Runtime Error '9': Subscript out of Range". If I type in
the value of the sheet directly in the code i.e "Sheets("Core
Locations").Select", I can get past the code but there are several
variables defined in the StartProgram Module and it make the code a
lot messy. I tried to copy all the variables and place them in the
same module as the Sub I am running but same error shows. It seems
that I am unable to access these variable in my Sub which apparetly
was not a problem in the past.

Thank you for your help again!!!