Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Default Worksheet when Opening.

Default Worksheet when Opening

by Allen Wyatt
(last updated December 24, 2011)

16

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:

  1. Make sure you have only a single Excel workbook open. While this isn't exactly mandatory, it will make creating the macro a bit easier.
  2. Press Alt+F11 to display the VBA Editor.
  3. In the Project Explorer window you will see a list of the open workbooks and templates. If the Project Explorer is not visible on your screen, choose Project Explorer from the View menu.
  4. Locate your current workbook in the Project Explorer. It will be named something like VBAProject (MyWorkbook), where "MyWorkbook" is the name of the actual workbook.
  5. If there is a plus sign to the left of the current workbook in the Project Explorer, click on it. You should see a list of worksheets appear underneath the workbook.
  6. If you don't see a list of worksheets, but instead see a list of folders with plus signs to their left, click on the plus sign to the left of Microsoft Excel Objects. Now you should see the worksheets.
  7. At the bottom of the list of worksheets is the ThisWorkbook object. Double-click on it. A code window is opened.
  8. In the code window, paste or create the macro shown above. Make sure you name it exactly as shown.
  9. Close the VBA Editor.
  10. Save your workbook.

Now, whenever you open the workbook, the specified worksheet will be displayed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2014) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Default Worksheet when Opening.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Controlling URL Formatting

When you type a URL into a document, Word helpfully converts it to a live hyperlink. If you don't want Word to be quite that ...

Discover More

Limits on Path Length in Word

When you organize your hard drive, it is easy to go hog-wild with folders and subfolders. You need to know that how you ...

Discover More

Protect Your Document Templates

If you want to protect your templates from accidental changes, the best way to do so is by using Windows instead of Word. ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (MENU)

Understanding Manual Calculation

When you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If ...

Discover More

Changing Gridline Color

Gridlines are very helpful in seeing where cells are located on the screen. You are not limited to black gridlines; here's ...

Discover More

Defining and Using Custom Colors

Want to spice up your worksheets with your own custom colors? Here's how to define them easily.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2017-04-14 07:45:10

BKNelson

Hi Allen - I had already changed "StartSheet" to the name of my worksheet that I'm wanting to open, per the instructions. No luck.


2017-04-13 12:59:36

Allen

BKN: Do you have a worksheet named "StartSheet"? If you don't, then the macro cannot activate a non-existent worksheet.

-Allen


2017-04-13 12:56:21

BKNelson

Getting a Run-time Error '9': Subscript out of range error...any ideas, please? Thanks!


2016-12-23 14:35:44

Alan Elston

Hi Barry,
I agree using the Code Name is a good idea – It was some time before I recently realised there was one – so a very good point that many will have unlikely changed it.
Alternatively if you know which Tab Number ( Index ) is always used, then you can use that. This +ve Integer Number is the position counting from the left of the Tabs shown at the bottom of the Excel Workbook Window, ( when all worksheets are visible ). Changing Name or Code Name of a Worksheet has no effect on this Number. The number only changes if you change the positions of a Tab, for example, by holding the left mouse down on the Tab and dragging it to a different shown position
Remember if using a variable for this number, be sure you dimension the variable as a number, oor convert it to a Number variable before using it as the Worksheet Index. If you do not do this, it may occur that the number is taken as a String, in which case you will be trying to reference a worksheet which has a String Name of that Number

Sub WorksheetsRef() ' Referrence 2nd Tab counting from the left.
Dim wsTab2 As Worksheet
Set wsTab2 = ThisWorkbook.Worksheets.Item(2)
Set wsTab2 = ThisWorkbook.Worksheets(2)
Dim Tab2No As Long
Let Tab2No = 2
Set wsTab2 = ThisWorkbook.Worksheets.Item(Tab2No)
Set wsTab2 = ThisWorkbook.Worksheets(Tab2No)

wsTab2.Activate
End Sub

In the above code you can, of course, remove ThisWorkbook if the code appears in the ThisWorkbook Class Code Module that has a name something like MyWorkbook or ThisWorkbook
_...............

One small point, - Regarding the idea of using The Code Name:
Remember if sharing a code rather than a complete Workbook, and relying on default settings, you will need to ensure that the recipient has the same language version. For example, In English Excel the Code Name of, for example, the First Tab is, I believe “Sheet1”. In my German Excel this it “Tabelle1”


Alan
Merry Xmas everyone :)


2016-12-22 06:02:26

Barry

@Rachel,

I appreciate it that you posted several months ago, but my response may help others with similar issue.

Two other contributors have mentioned possible problems Matt on 5-Jan-2016 and MilesS on 8-Aug-2014. As a supplement to Matt's post there is an issue which would cause your problem if someone changed the name of the Tab but didn't reflect this in the macro. The easiest approach is to use the worksheets codename to address it rather than the Tab name (the codename is not something an average (or most advanced) Users would know how to change).
The codename is created when the workbook is first created is the same as the tab name i.e. "Sheet1" has codename Sheet1, etc. and it can be found in the VB Editor navigation pane with the current Tab name next to it. So, for example, if the sheet you want always to be displayed first has the name "FirstSheet" and codename "Sheet1" then the workbook open macro should be written to "Sheet1.Activate".
The issue MileS discusses is still present and I would recommend implementing that as well as you will not know what the security settings are set to on another Users computer and therefore whether or not macros will be allowed to run or not.


2016-12-21 18:28:30

saeed ziedan

thank you very much for your achievements
you are talented person

i so much use your site and get a lot of useful thing for my work

thank you very much


2016-08-25 05:45:15

Rachel Cook

Ive done the above but when i re open my workbook it shows a run time erro 9 subscript out of range message. Any ideas?


2016-01-05 12:21:13

Matt

Reply to Anand.

Make sure there are no spaces in your sheet name. Try using a single word.


2015-06-18 04:09:45

Kuttotta

Wow at last I got a resolution for what I was looking for long time, thank you very much.


2014-08-08 02:33:46

MilesS

Reply to Meyn.
Yes very easy to setup.
1: On before_close, hide all windows except startup sheet.
2: Startup sheet says "switch on macros"
3: On_load unhides sheets and takes the user to the page you want to go to.

4: As creator, save the file. 1 above is triggered. New user opens the file and they only see the "enable macro sheet"

sample code below

'note a for each loop would have been better

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'note use a for each instead of specified below
Sheets("_instructions").Visible = True
Sheets("_instructions").Activate
Sheets("Clients").Visible = xlVeryHidden
'xlVeryHidden means programming change to open required

Application.StatusBar = " "
End Sub

Private Sub Workbook_Open()
Sheets("Clients").Visible = True
Sheets("Clients").Activate
Sheets("_instructions").Visible = False

'do whatever needs to be done here

End Sub


2014-07-18 01:28:53

Meyn

Hello,

this is always a good tip, when the user has already allowed to use macros on the specific document. But my point is as long the document has "never" been opened on the user's computer. So long he will see the last used sheet. Is there a way to force excel to open with a specific sheet without using VBA?


2013-04-03 04:17:38

Diogenes Ballesteros

I'm very thankful for this tip and finally I was able to make it work and submit my work to my superior.

Again, thank you very much.


2013-04-03 01:50:04

Anand

I have followed the procedure as you have stated, but my workbook still opens it in sheet where i saved it.. please help..


2013-02-28 01:52:54

Azeem

You would be very happy too see that ppl like me are still benefited from your site. Thanks a lot for your effort.
Your step by step explanation made a dump like me feel like a programmer hehe.


2013-02-21 23:20:32

Nina

This was excellent advice and simple to apply - worked a treat, thank you!


2011-11-19 08:03:30

Jordan Freedman

Will you point me toward some information about the basics of writing a macro using the VBA editor?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing