You'd have to open FileA.xls - this code, placed in a module of
FileB.xls, will open FileA.xls (or use it if it's already opened), use
a range called "NamedRange", and copy its content to Sheet2 in
FileB.xls, starting on A1. If FileA.xls is already open, it will
still perform the copy, but will not close the file.

Public Sub ImportNamedRange()
Dim rng As Excel.Range
Dim wkb As Excel.Workbook
Dim blnClose As Boolean

Const strName As String = "NamedRange"

blnClose = False
Set rng = ThisWorkbook.Sheets("Sheet2").Range("A1")

On Error Resume Next
Set wkb = Application.Workbooks("FileA.xls")
If Err.Number < 0 Then
Set wkb = Application.Workbooks.Open("FileA.xls")
blnClose = True
Err.Clear
End If
On Error GoTo 0

wkb.Names(strName).RefersToRange.Copy _
ThisWorkbook.Sheets("Sheet2").Range("A1")

If blnClose Then wkb.Close (False)
End Sub



I can't think of a simpler way to do this.


On Oct 18, 4:46 pm, EA wrote:
I have a named range in my 'FileA.xls' file that references an array of data.
But say if I'm working in 'FileB.xls' and 'FileA.xls' is closed, is there
any way in VB to call out and import the named range from 'FileA.xls' into
'FileB.xls'?