An Excel file can have multiple sheets in it. The sheets can be linked to each other, and to other files on your system. By default, when you open an Excel file, it opens to the sheet that you were last working in. If you need it to open a specific sheet whenever you open the file, you’re going to have to manually switch to it before you close it. This isn’t exactly intuitive since you may forget to switch to the sheet. You can automate it with a simple Macro and set a default sheet for an Excel file.
Default sheet for Excel file
Open the Excel file that you’d like to set a default sheet in. Sheets have a default name and it will do for the macro but, it is a good idea to give the sheets a proper name. If you plan on changing the name of a sheet later on, it is better to do it now so that the macro can be set up. Take note of the name of the sheet that you will set as the default.
Tap the Alt+F11 key. This will open the VBA editor. From the column on the left, make sure your Excel file is selected. Expand it, and select the ‘This Workbook’ option. Double-click it to open the input field in the pane on the right. Make sure the dropdown reads ‘Workbook’.
Paste the following in it but change the name of the sheet in quotes in the second line to the name of the sheet that you’d like to set as the default one.
Macro
Private Sub Workbook_Open() Worksheets("Default").Activate End Sub
Example
Private Sub Workbook_Open() Worksheets("Main").Activate End Sub
Save the file as a Macro enabled file. It will have the XLSM file extension.
This will work regardless of which system you open the file on. If you share this file with someone, and they open it on their own respective desktop, they will see a prompt telling them it has a Macro and it must be allowed to run. If the Macro is allowed to run, the file will open to the default sheet. If it is prevented from running, the file won’t open or if it opens, it will open to the last sheet that was edited. This is the one drawback that this method has.
If you are sharing the file, it’s a good idea to let your recipient know that it contains a Macro and what the Macro does.
