Referencing Workbook - Not working for coworker

Question

I'm using the following code to open up a workbook in the same folder as the current file given a file name entered into a cell. Everything is working for me, but when my coworker runs it, he gets a subscript out of range error for Workbooks(modFile). I verified that he has macros enabled. I also tried running it with the file extension added to the modFile string and it didn't work. Does anyone know why it could be working for me (and other coworkers) and not him and what I can do to fix it.

pathName = ActiveWorkbook.Path
current = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
modFileName = Sheets("Main").Range("B1").Value
modFile = Left(modFileName, Len(modFileName) - 5)
modFileAddress = pathName & "\" & modFileName

modFileExists = Dir(modFileAddress)

If modFileExists = "" Then
    MsgBox "Module Tracking File Not Found. Verify Name."
End If

ScreenUpdating = False
Workbooks.Open Filename:=modFileAddress
If (Workbooks(modFile).Sheets("Master").AutoFilterMode And Workbooks(modFile).Sheets("Master").FilterMode) Or Workbooks(modFile).Sheets("Master").FilterMode Then
    Workbooks(modFile).Sheets("Master").ShowAllData
End If
Workbooks(modFile).Sheets("Master").Range("A1").AutoFilter Field:=8, Criteria1:="80"
Workbooks(modFile).Sheets("Master").Range("A1").AutoFilter Field:=33, Criteria1:="0"
lastrow = Workbooks(modFile).Sheets("Master").Cells(Workbooks(modFile).Sheets("Master").Rows.Count, "A").End(xlUp).Row
Workbooks(current).Sheets("Master Data").Cells.Clear
Workbooks(modFile).Sheets("Master").Range("A1:AE" & lastrow).Copy Workbooks(current).Sheets("Master Data").Range("A1")
Workbooks(modFileName).Close SaveChanges:=False

Private Sub is located in Sheet1 triggered by a button click. I also verified that my coworker is putting the file name in the correct cell and it is longer than 5 characters.

Answer 1

There might be an issue here with how you are referencing your opened workbook. Each time you use Workbooks(modFile) you are searching for an index string in the list of open workbooks. If you search for that string in a list that doesn't contain that string, eg. it hasn't fully opened yet, it will throw an index out of bounds error. Do what BigBen said and replace all instances of Workbooks(modFile with a reference to a workbook variable.

dim wb as Workbook
set wb = Workbooks.Open("file\path\here...")
wb.Close SaveChanges:=False