run time error 9

[andysh]

Hi

I have the below code to copy data from one workbook to another but keep getting a subscript out of range error on the line...

Set wb = Workbooks("2019_-_Couriersheet_-_copy.xlsx").Worksheets("Triage2")​

Both workbooks are open and I've checked the file name is correct etc. What could the issue be?

Sub Export_data()

Dim ws As Worksheet, wb As Workbook

Set ws = Sheets("Triage")
Set wb = Workbooks("2019_-_Couriersheet_-_copy.xlsx").Worksheets("Triage2")
Dim lr As Long, lrw As Long, lc As Long

Application.ScreenUpdating = False

lrw = ws.Range("A" &Rows.Count).End(xlUp).Row

If lrw <> 1 Then

lc= ws.Cells(1, Columns.Count).End(xlToLeft).Column
lr= wb.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range(ws.Cells(2, 1), ws.Cells(lrw, lc)).Copy
wb.Range("A" & lr).PasteSpecial xlPasteValues
ws.Range(ws.Cells(2, 1), ws.Cells(lrw, lc)).EntireRow.Delete

End If


Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox" Exported ! "


End Sub

 

[Fluff]

You have declared wb as a workbook, but are trying to set it to a worksheet.
Change the declaration to WorkSheet

 

[andysh]

Tried that and I still get the same error?

 

[Fluff]

Ok, what happens if you run this

Sub chk()
 Workbooks("2019_-_Couriersheet_-_copy.xlsx").Activate
End Sub

 

[andysh]

I get the same error when I run the suggested code

 

[Fluff]

In that case that either that workbook isn't open, or it is spelt wrongly.
activate that workbook & run this

Sub chk()
 Debug.Print "|" & ActiveWorkbook.Name & "|", Len(ActiveWorkbook.Name)
End Sub

Then in the VB Editor look in the immediate window (normally below the main code window, Ctrl G will open it if needed) and copy what is printed there & post it here.

 

[andysh]

Ran the code and got...

|2019 - Couriersheet -copy.xlsx| 31

 

[Fluff]

Did you copy/paste that, or retype it?
The reason I ask is there are only 30 characters in the file name, but it's reporting 31

 

[andysh]

Copied the file name displayed and it's accepting it.

Just need to get the code right for the cut/paste now...

Thanks for your help Fluff

 

[Fluff]

You're welcome & thanks for the feedback