getting Run-time Error 9: Subscript out of Range when trying to get value from a range in another open workbook

Question

Whilst working in one workbook, I have code that puts the value of a named range in another open workbook (Workbooks("Draft Sales")) into a variable. I don't activate Workbooks("Draft Sales"). This works on my computer and one of my colleagues computers, but not on two other colleagues computers - where it brings up the error message "Run-time Error 9: Subscript out of Range". I don't understand what the difference is. We're all on the same version of Excel (Excel 2013, 32 bit), and other parts of the code work fine on all four computers.

Here are two examples of code that works for two of us but not the other two:

varFormula = Workbooks("Draft Sales").Worksheets("HoldingBay").Range("InvOrCreditGrossTotalFormula").Formula

dblChkCalcd = Workbooks("Draft Sales").Worksheets("HoldingBay").Range("CheckCalculationIsFinished").Value

I've confirmed on my colleagues' computers that the "Draft Sales" workbook is open in the same instance of Excel, by running code to list all the open workbooks, and this does pick up the "Draft Sales" workbook.

Can anyone suggest why the code isn't working on my colleagues' computers?

Answer 1

Need to include the workbook extension: Workbooks("Draft Sales.xlsx")