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.
Excel RANK, RANK.AVG and RANK.EQ Functions
Paul Wright
Brilliant. Easy to follow and delivered the required solution first time.
Thank you
Mynda Treacy
Great to hear, Paul!
Dixie Folzenlogen
Force Excel to Open on a Specific Sheet • My Online Training Hub
The above works beautifully. NOW, how do I also do the above and set all worksheets in the workbook to open at Cell A1?
Mynda Treacy
Hi Dixie,
Modify the code as follows:
Private Sub Workbook_Open() Worksheets("Example").Activate Worksheets("Example").Range("A1").Select End SubMynda
r.kadioglu
Thanks a lot. Saved my day!
Philip Treacy
You’re welcome.
Manoj Kumar
Very easily explained. Its working. Thank you
Mynda Treacy
Glad it was helpful, Manoj!
Ben
I did that but it does not work in 2019 version.
Mynda Treacy
Did you enable macros, Ben?
Gary Prater
Ok, next step is to get the sheet to open with today’s date centred at the top of the sheet (dates run in a row). How do I do this as today’s date will be ‘moving’ along the row?
Philip Treacy
Hi Gary,
Not sure what you mean by the dates ‘moving’ along the row.
Use TODAY() to get today’s date. Put it in a cell on the sheet and when you open the workbook it’ll show today’s date.
Regards
Phil
Steven Edgar
Excellent !!!
Worked first time and did exactly what I needed it too (using Excel 2013).
I wish all tips and guides were as easy to follow and to implement
Mynda Treacy
Thanks Steven, glad this was useful for you
jason
Thanks 🙂
Philip Treacy
You’re welcome.
BKNelson
Hi there – I’ve followed the instructions exactly, but am getting a Run-time error ‘9’: Subscript out of range error…any tips, please?? (I read other boards and others have had the same issue – haven’t been able to figure it out even with modifying the Trust Centre to allow for macros to run.)
Catalin Bombea
Hi,
Please upload a sample file so we can see where the code stops. Use our forum, create a new topic to upload file and add a description of the problem.
Cheers,
Catalin
Philip Treacy
Hi,
I’d guess the sheet you are trying to open does not exist. But as Catalin said, please create a topic on our forum and supply your file and code.
Regards
Phil
Orville
I had the same problem. Seems like it might be a version issue or something. Try this code, it worked for me:
Private Sub Workbook_Open()
Application.Goto Worksheets(“Index”).Range(“A1”)
End Sub
S K LOHAR
thanx a lot, it works.
Mynda Treacy
You’re welcome, SK 🙂
Colleen
Thanks! I saw the code on another site, but without the directions as to where it goes. I’m a newbie to VBA so this was perfect! Thanks for the assist!
Mynda Treacy
Glad I could help, Colleen 🙂
Greg
I get it. I know if I save from the desired start page the workbook will always open to that page also. I am making a workbook for others to use and they will be entering information on various pages then saving. Is there NO way to do this without opening the workbook up to code? I understand the risk is not that great but I work for a municipality and I can’t risk malicious code. It seems like there would be a simple way to force this without a macro.
Mynda Treacy
Hi Greg,
As far as I’m aware there’s no other way to achieve this other than a macro, or as you mention, saving the workbook on the desired start page before closing.
In terms of malicious code, if you set up the workbook and it is in your control, or other employees of the municipality, at all times then surely the risk of malicious code is very low.
Kind regards,
Mynda.
Mark Bishop
Hi Mynda
very late to this thread. Thanks for the code. Works fine!
But I’m with Greg. Why would Microsoft remove it from the Options for Excel? You can do it in Access (open to a specific form). And this way you get the added user hassle of having to enable macros!
Mr Bish
Mynda Treacy
Hi Mr Bish,
I don’t recall this ever being a feature in Excel. I could only check back as far as Excel 2007.
Mynda