Opening a workbook to a specific worksheet

Assuming you have one frequently used worksheet in a big workbook, wouldn’t you prefer to land on that specific worksheet when opening the workbook?  By creating the simple macro given below, one can land on a specific worksheet and even a specific cell when opening a workbook. Follow the steps below as we explain how this can be done.Â

Visual Basic Coding:Â

Private Sub Workbook_Open()

Sheets(“Sheet1″).Select

Range(“A1″).Select

End SubÂ

Applies To: Excel 2003, 2007, 2010

1. Open Microsoft Excel 
2. Press Alt + F11 
3. This will open the VISUAL BASIC Editor 
4. On the far left, under Microsoft Excel Object, select ThisWorkbook 
5. On the right hand side, paste the above coding
6. In the second line of the coding, change Sheet1, to the sheet name (tab) you wish to land on

7.  In the third line of the coding, change A1, to the cell you wish to land on

8.  From the Menu bar, select File, Close and Return to Microsoft Excel

9.  Save and close the Microsoft Excel workbook

10.  Ensure you save the workbook as an Excel Macro-Enabled Workbook (*.xlsm)

11.  The next time you open the workbook, it will open on the specific worksheet and cell

N.B Ensure that your Macro Settings are not disabled under the Macro Settings Option . To set your Macro security level, follow the steps below.

• On the Office/File tab, select Options
• On the left side of the dialogue box,select Trust Centre
• Select the Trust Centre Settings button
• On the left side, select Macro Settings
• Select Disable all macros with notification

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations

Further Reading:

1. Mandatory Cell Input
2. Mandatory Cell Input
3. How to create a shared workbook so that several people can edit the contents simultaneously
4. Publishing an Excel Workbook to an Intranet or Internet
5. Publishing an Excel workbook to an intranet or internet