This really is a very quick #macromonday tip for you. Have you got a large Excel workbook with lots of worksheets?. It’s sometimes really handy to be able to open your workbook straight to the worksheet you want to.
An Excel workbook will by default re open up on the last sheet that was active when it was saved. If you want to over ride this this you can do so with a small piece of VBA code.
What Does The Macro Do?
This macro will automatically go to the Sheet that you specify in the code. In this example it is Sheet1.
How Does It Work?
We use the Workbook_Open event which is a procedure of the Workbook Object, and specify in the code for Excel to select Sheet1 when the workbook is opened, thus overriding any default sheet that the workbook saved on. A really straightforward piece of VBA.
the Workbook_Open procedure MUST reside in the private module of the Workbook Object (ThisWorkbook).
You need to install this piece of code into Excel which is easy to do.
- Hit ALT + F11
- Click on ThisWorkBook in the Project VBA Window
- You will now see the This Workbook code module- double click to open it
- Enter or copy the above code into This Workbook code module making sure you change the sheet name you want the workbook to open up on
- Hit ATL+F11
- Save the the workbook
Want To Copy The Code?
‘macro by howtoexcelatexcel.com
Private Sub Workbook_Open()
Sheets(“Sheet1”).Select ‘Change this to your sheet name
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.