A few weeks ago one of our members, Perrin asked;
“Is there a way to force a workbook to always open on a specific sheet? I have a workbook that has a directory and I would like users to always be directed to this sheet first when opening the workbook.”
The answer is yes, but it requires a macro.
Hold up, it's not one of those scary complicated macros.
This one only has 3 lines and you can copy and paste it right into your workbook (well, actually you have to copy and paste it directly into your VBA editor to be exact, but I didn't want to scare you off with the 'VBA' word).
Here it is:
Private Sub Workbook_Open() Worksheets("Example").Activate End Sub
Change the sheet name (in double quotes above) to match the one you need.
Now to put the macro into your workbook:
1. Press ALT+F11 to open the VBA Editor.
2. Look for the name of your workbook in the left pane. Mine is called Book2.
3. Click on the + sign to expand the view (if it isn't already) and double click on ‘ThisWorkbook’.
4. In the right pane there are two dropdowns at the top. Make sure the left one says ‘Workbook’.
5. Now copy and paste the code above into the right pane.
6. Change the name in double quotes to match the worksheet you want to open. Mine is called ‘Index’.
7. Save your workbook as a 'Macro enabled workbook' with file extension .xlsm
Now when the workbook opens it will open to your desired sheet once Macros are enabled.
Easy peasy lemon squeezy.