Resetting Multiple Sheets on Workbook Open

[drewberts]

I'm trying to set zoom level as well as each sheet having the last empty cell selected. I have a button on each sheet which manually selects and scrolls to the last cell using:-

ActiveSheet.ShowAllData
Range("Table1").Cells(1, 2).End(xlDown).Offset(1).Select
ActiveWindow.ScrollColumn = 1

This works fine when clicking the button but I want to automate this for each sheet on workbook_open. I'm using the following (which I know is wrong, but I don't know why):-

Private Sub Workbook_Open()
On Error Resume Next
Application.ScreenUpdating = False

Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
ActiveWindow.Zoom = 75
ActiveSheet.ShowAllData
Range("Table1").Cells(1, 2).End(xlDown).Offset(1).Select
ActiveWindow.ScrollColumn = 1
Range("Table2").Cells(1, 2).End(xlDown).Offset(1).Select
ActiveWindow.ScrollColumn = 1
Range("Table3").Cells(1, 2).End(xlDown).Offset(1).Select
ActiveWindow.ScrollColumn = 1
Next ws

Application.ScreenUpdating = True
End Sub

 

[jproffer]

Have you tried stepping through it manually to see what it does? You'll have to put it into a standard module for that, but it should show you what's "not working". To me, it looks like it should do what your individual 3 line code does only in each worksheet.

You might comment out the "on error resume next" and "screenupdating" lines and make sure it's not finding an error and skipping something.