Workbook_Open() Not Working for Other Users, But Works for Me!

[Bayport_Mama]

I have a workbook that is saved in a public network folder for others to open. Whenever I open the workbook (as if I were an end user), the code works flawlessly, however, if anyone else at my work tries to open it, they immediately get Run-time error '1004' Application-defined or Object-defined.

My code is stored in the ThisWorkbook object. Here's my code:

Private Sub workbook_open()

MsgBox "Update in progress, hang tight! ", vbOKOnly

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Worksheets("Completed Reviews1").Range("A:F").ClearContents
Worksheets("Completed Reviews2").Range("A:F").ClearContents
Application.DisplayAlerts = False
Application.CutCopyMode = False
Application.EnableEvents = False

Workbooks.Open ("S:\locationoffile")
Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Range("a1").End(xlDown).Offset(0, 5)).Copy

ThisWorkbook.Worksheets("Completed Reviews2").Range("a1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


Call Update_Officer_Reviews

Workbooks("Information 2.xlsx").Close

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

MsgBox "Update complete." & vbCrLf & vbCrLf & _
"Please select your name from the" & vbCrLf & _
"dropdown list to the left.", vbOKOnly

End Sub

Sub Update_Officer_Reviews()

Application.CutCopyMode = False

Workbooks("Information 2.xlsx").Worksheets("Sheet1").Select
Workbooks("Information 2.xlsx").Worksheets("Sheet1").Range("a1", Range("a1").End(xlDown).Offset(0, 5)).Copy

ThisWorkbook.Worksheets("Completed Reviews1").Range("a1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Current Reviews").Activate

End Sub

Anyone have suggestions on how to fix this??? I'm so confused as why it would work for me, but not others at my work.

 

[Bayport_Mama]

That copy line is wrong. It should be:

Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1").End(xlDown).Offset(0, 5)).Copy

RoryA,

Do you have any ideas as to why the code was working for me, but not others??? Seems strange that I did not receive errors before the code was fixed.

 

[RoryA]

I suspect you had Sheet2 active when the code ran, whereas they did not.

 

[Bayport_Mama]

I suspect you had Sheet2 active when the code ran, whereas they did not.

Negative.

All Excel workbooks were closed when the code ran, so I'm not sure what was happening for it to work on my end.

 

[RoryA]

I mean sheet2 in the workbook that gets opened. That’s the only way the code would not error. If you opened it when it had been saved with sheet2 active, it would work. If it had been saved with a different sheet active, the code would fail.