Runtime Error '9' Subscript out of range error

[tomsov]

Hello, this is driving me crazy as I've checked numerous times that my file location and name is correct. This bit of code keeps throwing the error 'Runtime Error 9, subscript out of range' and I can't fathom the reason why - I've checked and double checked that the file names, location, extension is exactly right as to what and where it is on my PC but continue to get the error. The error is always on the the Set wsDest = part of the code. Help much appreciated!

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = ThisWorkbook.Worksheets("Sage Output")
Set wsDest = Workbooks("C:\Users\minee\Desktop\InvoiceRecordSheet.xlsx").Worksheets("SageBook")

... [code to copy etc]

 

[GWteB]

Try this

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
dim wbDest as Workbook

Set wsCopy = ThisWorkbook.Worksheets("Sage Output")
set wbDest = Application.Workbooks.Open("C:\Users\minee\Desktop\InvoiceRecordSheet.xlsx")
Set wsDest = wbDest.Worksheets("SageBook")

 

[Fluff]

The file has to be open before you can reference it like & then you just use the name of the workbook, not the full path.

 

[tomsov]

GWteB, again - absolute star!!!! Worked perfectly! You are a master at digging me out of holes!

Thank you, it is really appreciated.

 

[GWteB]

Am I? You're welcome

 

[tomsov]

Sorry to bother you again, then to save and close the workbook I'm using this but getting the error: Error '13' Type Mismatch on the Application.Workbooks Close statement.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim wbDest As Workbook

Set wsCopy = ThisWorkbook.Worksheets("Sage Output")
Set wbDest = Application.Workbooks.Open("C:\Users\minee\Desktop\InvoiceRecordSheet.xlsx")
Set wsDest = wbDest.Worksheets("SageBook")

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

wsCopy.Range("A3:J" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)

Application.Workbooks(wbDest).Close SaveChanges:=True

 

[GWteB]

try

wbDest.Close SaveChanges:=True

 

[tomsov]

GWteB, yes that worked.

Thank you!