Runtime error 9 - subscript out of range

[Afro_Cookie]

We use a macro daily to update some of our production files and when we run the macro, it only works on some computers. We've been using the same computer to run this and other queries for over a year and until this month, we have not had any issues. I can't determine why one computer would be able to run the macro while another cannot.

Below is the code and when I run the code line by line, it stops at tbl.Copy Workbooks("report").Sheets("Sort").Range("A1"). Any help possible would be greatly appreciated.

Sub gather_information()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open "source file"

Dim tbl As Range
Set tbl = Sheets("main").AutoFilter.Range
Set tbl = tbl.Resize(tbl.Rows.Count - 1)
Set tbl = tbl.Offset(1)

Sheets("main").Select

ActiveSheet.Range("$A$1:$J$1").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Date)
tbl.Copy Workbooks("report").Sheets("Sort").Range("A1")

Workbooks("source file").Close SaveChanges:=False

End Sub

 

[Peter_SSs]

My first guess is that it is a Windows Explorer setting issue on the other computer and that this thread might help you.

 

[Fluff]

You need to add the file extension to the name of the workbook.

 

[Afro_Cookie]

You need to add the file extension to the name of the workbook.

I changed the name of the file in my post as I can't share the actual path. This is what it roughly looks like "P:\folder\sub folder\sub sub folder\file.xls"

As for the file extensions, I don't have that option for some reason. This is what my 'view' tab looks like

 

[Fluff]

It's nothing to do with Excel, you need to add the extenstion to the workbook name in your code

Workbooks("report.xlsx").Sheets("Sort").Range("A1")

 

[Afro_Cookie]

It's nothing to do with Excel, you need to add the extenstion to the workbook name in your code

Workbooks("report.xlsx").Sheets("Sort").Range("A1")

I added the extension to the file path and it appears to have worked. I'm curious though as to why this would affect 1 computer and not another when the same code is being run.

 

[Fluff]

As Peter said, it's down to the Windows Explorer settings. If you have "Hide known file extensions" checked, then you do not need the extension, other you do.