Oops, I've been a bit thick.
The problem is that it should be Workbooks.Open not Workbook.Open.
If you change that you'll also find another problem, merged cells.
The named range 'fh4aAppraisalPath' covers the merged cells D8:I8, so Range("fh4aAppraisalPath") will return an array of values from those cells.
To only get the value you can use this.
fund = Range("fh4aAppraisalPath").Cells(1, 1).Value
So the start of the code would look like this.
ThisWorkbook.Worksheets("FH4A Appraisal").Select
fund = Range("fh4aAppraisalPath").Cells(1, 1).Value
'Open the file
copyaddress = fund
Workbooks.Open Filename:=copyaddress
Or if you wanted to tidy things up a bit.
Sub LoadFH4AData()
Dim wbOpen As Workbook
Dim fund As String
'Assign address file to a variable
fund = Range("fh4aAppraisalPath").Cells(1, 1).Value
Set wbOpen = Workbooks.Open(Filename:=fund)
'Add appraisal data to Appraisal macro sheet
With ThisWorkbook.Sheets("FH4A Appraisal")
.Visible = True
wbOpen.Sheets(1).Range("A20:AA10020").Copy
.Range("A20").PasteSpecial Paste:=xlPasteFormulas
End With
'Add FX Data to FX tab
With ThisWorkbook.Sheets("FH4A Pending FX")
.Visible = True
wbOpen.Sheets(1).Range("A1:Y10000").Copy
.Range("A20").PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
'Close MCH Appraisal workbook without saving
wbOpen.Close SaveChanges:=False
End Sub
Bookmarks