When you open a workbook, Excel normally displays the worksheet last displayed when the workbook was last saved. You may want a specific worksheet to always be displayed when the workbook is opened, regardless of the worksheet displayed when the workbook was last saved.
You can control which worksheet is displayed by using this macro:
Private Sub Workbook_Open() Worksheets("StartSheet").Activate End Sub
This macro will always display a worksheet named StartSheet. You will obviously need to change the worksheet name to something different; it should exactly match the name of the desired worksheet.
For this macro to work properly, it has to be associated with the workbook object. Follow these steps:
Now, whenever you open the workbook, the specified worksheet will be displayed.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11146) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Default Worksheet when Opening.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When developing macros, you can create subroutines. This is a great way to reuse common code and make your programming ...
Discover MoreWant to figure out the day of the month represented by a particular date? You can use the Day function in VBA to get the ...
Discover MoreIf your macro closes workbooks, you'll want to make sure that it will save any changes you made to the workbook. Here's ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-11-09 09:51:34
Stefan
Hi there,
Some of my colleagues work on Excel using iPads, which means it doesn't support the use of Macros.
Is there a work around to achieve this by not using a macro?
Thanks.
Stefan
2016-08-23 03:23:01
edwin
Hi Allen,
I used the following codes and could not open my default worksheet. Can you please help.
Sub Workbook_Open()
Worksheets("Test").Activate
Range("B1").Select
End Sub
Many thanks
Edwin
2016-08-09 05:56:22
Al:
Where may I ask you a question regarding EMAIL MERGE - using Word for a document, Excel as the "database" and Outlook to send out emails? I know how to do email merge for one record = one row of info. But when it comes to inserting multiple rows of information for the same record, well, after 3 days of searching, I'm thoroughly lost!! Thank you, Mort
2016-08-09 02:29:54
Deepak
Great, worked perfectly!! Thanks a lot for your help..
2016-07-15 05:32:36
Preetam
The code you cite needs to be located on the “ThisWorkbook” codepage.
2016-07-14 11:37:04
Hi Allen
I have a workbook and I want to default to the "Control Panel" tab when a user opens the spreadsheet / workbook. I've used the code above but it does not work
Private Sub Workbook_Open()
Worksheets("Control Panel").Activate
End Sub
The code is in a module or does have to be in the worksheet?
2015-07-06 10:39:14
David Smith
This is a great tip -- I like to create an index/summary page for all of my workbooks that provides documentation of the contents, such as a table of contents, FAQ, or warnings. Now my users will have to look at this page every time they open the workbook.
2015-07-06 09:40:00
When I receive an attachment in excell format, I can't seem to open that worksheet.
I get everything from the past and I fumble around sometimes for half an hour trying to open what was just sent to me.
Is there a way to get the current attachment to open immediately?
2015-07-06 08:57:49
James
Cheers, worked a treat. Needed to save it as a macro-enabled file.
2015-07-06 06:13:39
Derek Bates
Short & sweet. But none the less useful.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments