Open Worksheet to Specific Position

Yes. You need to add code to the worksheet_activate event, for the specific worksheet, inside the visual basic editor.

Do you want the top-most cell to be Z1?

Example: Look at the attached file - see what happens when you 'select' the tab named SELECT ME.

Private Sub Worksheet_Activate()
    ActiveWindow.SmallScroll Up:=Rows.Count, toleft:=Columns.Count
    ActiveWindow.SmallScroll toRight:=25
    Me.Cells(1, 26).Select
End Sub

I'll give you the poor man's simple way, before closing it go to column z then save it and close. Next time you open it it should be where you want it.

Oh, you can also use custom views to set up the view you want.

@steveal

However, if I save and close the workbook with the worksheet anywhere other than column Z (say column C), it reopens to column C.

The reason that is happening, is because the workbook is opening with the worksheet already selected. In other words, the worksheet_activate() event is not being fired.


Here's what you can do (all this is done in the attachment).

1) Move the code to a new module - because we are going to be calling it from multiple events and only want to make changes in one place. In the attached file I moved the code to 'Module1'

Option Explicit

Sub SET_INITIAL_VIEW()
'Sets view of activesheet'
    ActiveWindow.SmallScroll Up:=Rows.Count, toleft:=Columns.Count
    ActiveWindow.SmallScroll toRight:=25
    ActiveSheet.Cells(1, 26).Select
End Sub

2) Edit the code of the TARGET worksheet object to call this routine in the worksheet_activate event.

Option Explicit

Private Sub Worksheet_Activate()
    Call SET_INITIAL_VIEW
End Sub

3) Finally - add a workbook_open() event to the 'ThisWorkbook' code module as follows. NOTE: I gave the target sheet a 'codeName' of w_TargetSheet (in the VBA project-properties). You would have to do the same in your workbook to refer to it like I have below.

Also - I don't know how many worksheets are in your workbook, or what you want to happen if they save the workbook with another sheet active -- that red part of the event just forces w_targetSheet to be active when opening the book (you can just take that out if you don't want to do that).

Private Sub Workbook_Open()

If ActiveSheet.Name = w_TargetSheet.Name Then
    Call SET_INITIAL_VIEW
Else
    w_TargetSheet.Activate
End If

End Sub