This is a start, but may need some tweaking to get what you want. Here I've assumed the sheet of interest is Sheet4 (code name of the sheet). The first event macro goes in Sheet4, the second goes in Thisworkbook.I'd like a worksheet to open with the window resized to display only the range A1:G16. The zoom needs to be 140%. Help with VBA please?
Private Sub Worksheet_Activate()
Dim R As Range
Application.ScreenUpdating = False
Set R = Me.Range("A1:G16")
R.Select
With ActiveWindow
.WindowState = xlNormal
.Top = R.Top
.Left = R.Left
.Width = R.Width + 200 'Adjust the added value to suit
.Height = R.Height + 160 'Adjust the added value to suit
.Zoom = 140
End With
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
If Sh.CodeName <> "Sheet4" Then
ActiveWindow.WindowState = xlMaximized
End If
Application.ScreenUpdating = True
End Sub