Copying contents of named range from one workbook to another with identical structure

[OxfordCurmudgeon]

I have a complex workbook (13 worksheets, scads of formulas, lots of data, modest amount of VBA code) developed for a client. Buried in the mess is a named range of data which the client updates regularly. When I send the client a new version of the workbook, I want it to be easy for them to copy the data in that named range from the old workbook to the new. It ought to be a piece of cake: Open the old workbook, copy the data from the named range to an array, close the old workbook and move the data from the array into the named range of the new workbook.

One wrinkle: The named range has workbook scope -- and might not be on the same sheet in the old workbook as on the new one.

The problem: Every technique I have tried to copy from the named range in the old workbook ends up taking the data instead from the same named range in the current workbook.

Here's the current code:

SrceWorkbookFilespec = _
    Application.GetOpenFilename("Excel Files (*.xlsm),*.xlsm", , "Open workbook with data to copy")
If SrceWorkbookFilespec = False Then Exit Sub ' Give up
Set SrceWorkbook = Workbooks.Open(SrceWorkbookFilespec, , True)   ' Open as read-only
With SrceWorkbook
'    Debug.Print .Name
    DataHold = Range(SrceWorkbook.Names("EmployeeInfo").RefersTo).Value2
    .Close SaveChanges:=False
End With

When the Debug.Print command isn't commented out, it shows -- as expected -- the name of the old workbook. But after the next line executes, DataHold has the data from the EmployeeInfo named range in the new workbook, not the old.

The VBA code is running from the new workbook. In my current testing, the new and old workbooks have nearly identical structure, so the named range refers to the same cell range ($A$2:$B$200) on the same sheet name.

And just to round out my frustration, when I add this

Range(ActiveWorkbok.Names("EmployeeInfo").RefersTo).Value2

to the Watch window, it shows me the data from the old workbook. But if I change "SrceWorkbook" to "ActiveWorkbook" in the DataHold = statement, DataHold still ends up with the data from the new workook not the old.

 

[Alex Blakenburg]

What happens if you put a period "." in front of the Range so that is uses the With statement

DataHold = .Range(SrceWorkbook.Names("EmployeeInfo").RefersTo).Value2

 

[Alex Blakenburg]

Ignore my previous comment.
Since SrceWorkbook should be the active workbook when you are retrieving the named range, you should only need this:

    DataHold = Range("EmployeeInfo").Value2