Copy data from closed workbook without opening and into next empty row

[luckyduck03]

I've got this code from another post on this forum that takes data from a closed workbook and copies it to sheet 5 of current workbook, using range A5:F14,:

Sub AddDataRangeFromClosedWorkbook()

Dim mydata As String
mydata = "='C:\[Closed Workbook.xlsm]MATLIST.XLS'!$A$5:$F$14" '
With ThisWorkbook.Sheets(5).Range("A5:F14")
    .Formula = mydata
    .Value = .Value
End With

End Sub

I'm trying to modify the code to copy a specified range from Closed Workbook.xlsm into sheet 5 of the current workbook, using the next empty row(I'm using Column E to determine the next empty row), below is what I have so far, but this places the value "='C:\[BAH BOM.xlsm]MATLIST.XLS'!$A$5:$F$14" into the next empty row of Column E, I'm trying to get this to grab data in cells A5:F14 from Closed Workbook.xlsm and pasts it into the same columns of A through F, while using Column E to determine the next empty row.

Sub AddDataRangeFromClosedWorkbook()

Dim mydata As String
mydata = "='C:\[Closed Workbook.xlsm]MATLIST.XLS'!$A$5:$F$14" '
With ThisWorkbook.Sheets(5).Range("E" & Rows.Count).End(xlUp).Offset(1)
    .Formula = mydata
    .Value = .Value
End With

End Sub

Any help is much appreciated.
Mark
Office 2013

 

[Fluff]

How about

Sub AddDataRangeFromClosedWorkbook()

Dim mydata As String
mydata = "='C:\[Closed Workbook.xlsm]MATLIST.XLS'!$A$5:$F$14" '
With ThisWorkbook.Sheets(5).Range("E" & Rows.Count).End(xlUp).Offset(1,-4)
    .Formula = mydata
    .Value = .Value
End With

End Sub

 

[luckyduck03]

How about

Sub AddDataRangeFromClosedWorkbook()

Dim mydata As String
mydata = "='C:\[Closed Workbook.xlsm]MATLIST.XLS'!$A$5:$F$14" '
With ThisWorkbook.Sheets(5).Range("E" & Rows.Count).End(xlUp).Offset(1,-4)
    .Formula = mydata
    .Value = .Value
End With

End Sub

Thank you for the suggestion, however it's still returning the formula ='C:\[Closed Workbook.xlsm]MATLIST.XLS'!$A$5:$F$14 into the next empty row instead of the actual data from cells:A5:F14 from Closed Workbook.xlsm. Only this time it's putting in on the A column, which is the correct column this data starts in.
Do I need to change how I collect the data from the MATLIST.XLS sheet of Closed Workbook.xlsm?

Mark
Office 2013

 

[Fluff]

How about

Sub AddDataRangeFromClosedWorkbook()

Dim mydata As String
mydata = "='C:\[Closed Workbook.xlsm]MATLIST.XLS'!A5" '
With ThisWorkbook.Sheets(5).Range("E" & Rows.Count).End(xlUp).Offset(1,-4).Resize(10,6)
    .Formula = mydata
    .Value = .Value
End With

End Sub

 

[luckyduck03]

This did the Trick! Thank you for the help!

 

[Fluff]

You're welcome & thanks for the feedback.