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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
There are a variety of ways that you might want to count the cells in your worksheet. One way is to figure out how many ...
Discover MoreIf you have a range of cells in which you want to count all the commas, there are several ways you can derive the figure ...
Discover MoreWhen you add a new worksheet to a workbook, Excel gives it a default name that consists of "Sheet" followed by a number. ...
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