Open Worksheet to Specific Position

[steveal]

I have a worksheet with data from Column A to column AP.
I 'always' want to work in the data in columns Z to AG.

Is there a way to ensure that the worksheet always opens with column Z at the left of the visible window?

Thanks,
Steve

[GeneralDisarray]

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.

Please Login or Register  to view this content.

[steveal]

Thanks. This works when I have the workbook open and click on the relevant worksheet tab.
However, if I save and close the workbook with the worksheet anywhere other than column Z (say column C), it reopens to column C.

[Sam Capricci]

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.

[Sam Capricci]

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

[GeneralDisarray]

@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'

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

[steveal]

GeneralDisarray,

Thanks for that - it works perfectly! (although it was on the limit of my poor Excel skills to follow your instructions).

Steve