Copy data from another file

[JackDanIce]

Hi,

I'm sure this has been asked before but I can't seem to find the answer in the search function on this site or Google; I'm probably not asking correctly.

I have the following code:

Sub Step2_ImportEFHoldings()

Dim archiveworkbook As Workbook
Dim SecurityNames As Range, SecurityHoldings As Range

Set currentworkbook = ActiveWorkbook

Application.DisplayAlerts = False

msg = "Please enter a valid file date"
With Sheets("MS File")
    .Range("FileDate").Select
    If Len(.Range("FileDate")) < 10 Then
        MsgBox msg, vbExclamation, "Invalid date"
        Exit Sub
    End If
    
End With

On Error GoTo WorkbookError
Workbooks.Open Filename:=Range("FileName"), UpdateLinks:=True, ReadOnly:=True
Set archiveworkbook = ActiveWorkbook
On Error GoTo 0

Range("CLOSING_NAV").Copy
currentworkbook.Activate
Range("FileNAV").PasteSpecial Paste:=xlValues
archiveworkbook.Close savechanges:=False

Exit Sub
WorkbookError:
msg = "File not found, please check"
MsgBox msg, vbExclamation, "File not found"

End Sub

Range("CLOSING_NAV") is local to the workbook that is opened.
I would rather have some code along the lines of:

currentworkbook.Range("FileNAV") = archiveworkbook.Range("CLOSING_NAV") but when I try this (as well as workbook.Sheets("sheet name").Range("FileNAV") = etc..) I keep getting an error message of invalid range or object

Can someone explain how I should be writing this?

Thanks,
Jack

 

[Warship]

Try something like:

'change "Sheet1" to suit
currentworkbook.Sheets("Sheet1").Range("FileNAV") = Evaluate(archiveworkbook.Names("CLOSING_NAV").Value)

 

[JackDanIce]

Hi Warship,

Thanks for the reply, but unfortunately, it's returns a value of #REF!. Any suggestions?

Best,
Jack

 

[Warship]

Only thing I can think of at the moment is to double check spelling and existance of named ranges.

 

[JackDanIce]

Did that twice before posting it didn't work..!

 

[JackDanIce]

Bump: any suggestions or can anyone else help?

 

[Warship]

The previous code I posted works for me.
There are other methods to retreive data from other books without opening the source book.
Have a look at:
http://spreadsheetpage.com/index.php...a_closed_file/
and
http://www.mrexcel.com/forum/showthread.php?t=568134

 

[JackDanIce]

Thanks Warship, will have a read and hopefully find a solution.
Best,
Jack