CODEWITHSUNDEEP
vbaexcel
Bryony565
Bryony565

Reputation: 55

Referencing other workbook works for most but gets "subscript out of range" error for two machines

I have a file which is used by 70 people (identical file, each team has their own copy for their geographical zone). This file works for 68 of the people. When the two that don't work log on a different machine it works. I can't see any difference in settings. "ignore other applications.." is unticked and the other file opens in the same instance of Excel.

Below is an extract of the code. The file opens ok, but when it gets to the line With Workbooks("Master").... it gets a "subscript out of range" error. I've tried doing it without using a "With" statement but that didn't help.

I can step through all the rest of the code, it's just the lines that refer to the "Master" work book that error (and they work fine on the other 68 machines).

            If IsAlreadyOpen("Master.xlsm") = False Then

            Workbooks.Open (filepath), ReadOnly:=False, UpdateLinks:=False, Password:="password here"

            Else
            Workbooks("Master.xlsm").Activate
            End If

              With Workbooks("Master").Sheets("This Sheet")
                            .Cells.EntireRow.Hidden = False
                            .Cells.EntireColumn.Hidden = False
                            .AutoFilterMode = False

                End With

Can anybody suggest anything to fix this?

Upvotes: 1

Views: 75

Answers (1)

David Zemens
David Zemens

Reputation: 53663

This would be an appropriate place to use object variable (to represent a Workbook object) instead of relying on Activate methods. Note this may require modifications elsewhere, if you are relying on ActiveWorkbook or unqualified Sheets, etc.

 Dim wbMaster as Workbook
 If Not IsAlreadyOpen("Master.xlsm") Then
    Set wbMaster = Workbooks.Open(filepath, ReadOnly:=False, UpdateLinks:=False, Password:="password here")
 Else:
    Set wbMaster = Workbooks("Master.xlsm")
 End If
 With wbMaster.Sheets("This Sheet")
     .Cells.EntireRow.Hidden = False
     .Cells.EntireColumn.Hidden = False
     .AutoFilterMode = False
 End With

Upvotes: 1

Related Questions