[VBA] Run-time error '1004'

[Martin_H]

Hi team,

this is the error I am randomly getting while opening my Excel file with macros (.xlm).

This is the opening procedure.

Private Sub Workbook_Open()
With Application
.ScreenUpdating = False: .WindowState = xlMaximized: .DisplayStatusBar = True

Call OptimizeCode_Begin

Worksheets("WELCOME").Activate
With ActiveWindow
Worksheets("WELCOME").Range("A1:AD1").Select
.DisplayHorizontalScrollBar = False: .DisplayVerticalScrollBar = False: .DisplayWorkbookTabs = False: .ZOOM = True: .ScrollRow = 1: .ScrollColumn = 1
Worksheets("WELCOME").Range("A1").Activate
End With

Call OptimizeCode_End

.ScreenUpdating = True
End With

End Sub

Here is the macro OptimizeCode_Begin & OptimizeCode_End.

Sub OptimizeCode_Begin()
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
Application.DisplayFormulaBar = False
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
End Sub


Sub OptimizeCode_End()
ActiveSheet.DisplayPageBreaks = PageBreakState
With Application
.Calculation = CalcState: .Calculation = xlCalculationAutomatic: .EnableEvents = EventState: .EnableEvents = True
End With
End Sub

What could cause this error?

TY!

 

[Joe4]

I would recommend copying your "Workbook_Open" procedure code and pasting it in a manual VBA code block, like this:

Sub Test()

With Application
.ScreenUpdating = False: .WindowState = xlMaximized: .DisplayStatusBar = True

Call OptimizeCode_Begin

Worksheets("WELCOME").Activate
With ActiveWindow
Worksheets("WELCOME").Range("A1:AD1").Select
.DisplayHorizontalScrollBar = False: .DisplayVerticalScrollBar = False: .DisplayWorkbookTabs = False: .ZOOM = True: .ScrollRow = 1: .ScrollColumn = 1
Worksheets("WELCOME").Range("A1").Activate
End With

Call OptimizeCode_End

.ScreenUpdating = True
End With

End With

Then, manually run this code by stepping through it one line at a time using the F8 key, and see which line of code the error occurs on.
That should give you a good idea of where exactly the error is occurring, and what line of code to focus your attention on.
Often times the error becomes evident when you see exactly what is happening (i.e. maybe the worksheet is hidden, range is protected, etc).