Random ScrollArea Errors

[mykulpasskwa]

I'm trying to lock the scroll area on multiple sheets and have it set for when the workbook opens. I've tried a few different kinds of code with varying degrees of success, but all end with getting an error eventually.

The one below is the most recent one I've tried, and it was working fine for the past two days and then today I got a Run-time error' 57121': Application-defined or object-defined error. I used to get the same error with the slightly different code I was using before, but I can't seem to figure out why this randomly happens. When I End debugging mode and run the macro on its own it works fine, but when I open it from scratch I get the error.

Private Sub Workbook_Open()Dim ws As Worksheet


Set ws = ActiveSheet


wksCalc.ScrollArea = "A1:S35"
wksSemester1.ScrollArea = "A1:M33"
wksSemester2.ScrollArea = "A1:M33"
wksSemester3.ScrollArea = "A1:M33"
wksSemester4.ScrollArea = "A1:M33"


Set ws = Nothing
        
End Sub

Can anyone see what's wrong or know the nature of the error? Google and forum searches haven't left me with many answers.

 

[My Aswer Is This]

Try something like this:

Sheets("Calc").ScrollArea = "A1:S35"
Sheets("Semester1").ScrollArea = "A1:M33"
Sheets("Semester2").ScrollArea = "A1:M33"
Sheets("Semester3").ScrollArea = "A1:M33"
Sheets("Semester4").ScrollArea = "A1:M33"

 

[My Aswer Is This]

Or if you wanted to do all your sheets you could try this.
It will set all the sheet scroll areas to the same scroll area but will then reset the ones you want different.
Add those sheet names outside the loop.

'Modified 5/16/2018 4:30 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).ScrollArea = "A1:M33"
Next
Sheets("Calc").ScrollArea = "A1:S35"
Application.ScreenUpdating = True

 

[mykulpasskwa]

Or if you wanted to do all your sheets you could try this.
It will set all the sheet scroll areas to the same scroll area but will then reset the ones you want different.
Add those sheet names outside the loop.

'Modified 5/16/2018 4:30 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).ScrollArea = "A1:M33"
Next
Sheets("Calc").ScrollArea = "A1:S35"
Application.ScreenUpdating = True

This is perfect. Thank you so much!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

This is perfect. Thank you so much!