• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Force Excel to Open on a Specific Sheet

You are here: Home / Excel / Force Excel to Open on a Specific Sheet
Force Excel to Open on a Specific Sheet
April 25, 2012 by Mynda Treacy

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’.

Excel Open specific sheet macro

7. Save your workbook as a 'Macro enabled workbook' with file extension .xlsm

Excel Open specific sheet macro

Now when the workbook opens it will open to your desired sheet once Macros are enabled.

Easy peasy lemon squeezy.

Force Excel to Open on a Specific Sheet
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Macros Posts

Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

Automatically highlight cells containing formulas in Excel to reduce the chance of them being deleted or over-written

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting

10 Mistakes to Avoid With Excel Formatting

These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category: ExcelTag: Macros
Previous Post:Excel RANK, RANK.AVG and RANK.EQ FunctionsExcel RANK, RANK.AVG and RANK.EQ Functions
Next Post:Excel 3D SUMIF Across Multiple WorksheetsExcel 3D SUMIF Across Multiple Worksheets

Reader Interactions

Comments

  1. Paul Wright

    August 23, 2022 at 10:34 pm

    Brilliant. Easy to follow and delivered the required solution first time.
    Thank you

    Reply
    • Mynda Treacy

      August 24, 2022 at 11:12 am

      Great to hear, Paul!

      Reply
  2. Dixie Folzenlogen

    March 11, 2022 at 11:30 pm

    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?

    Reply
    • Mynda Treacy

      March 13, 2022 at 10:38 am

      Hi Dixie,

      Modify the code as follows:

      Private Sub Workbook_Open()
       
      Worksheets("Example").Activate
      Worksheets("Example").Range("A1").Select
      
      End Sub

      Mynda

      Reply
  3. r.kadioglu

    October 5, 2021 at 10:30 pm

    Thanks a lot. Saved my day!

    Reply
    • Philip Treacy

      October 6, 2021 at 8:53 am

      You’re welcome.

      Reply
  4. Manoj Kumar

    May 8, 2021 at 9:46 pm

    Very easily explained. Its working. Thank you

    Reply
    • Mynda Treacy

      May 8, 2021 at 10:51 pm

      Glad it was helpful, Manoj!

      Reply
  5. Ben

    December 15, 2020 at 5:46 pm

    I did that but it does not work in 2019 version.

    Reply
    • Mynda Treacy

      December 16, 2020 at 10:26 am

      Did you enable macros, Ben?

      Reply
  6. Gary Prater

    November 14, 2019 at 11:26 am

    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?

    Reply
    • Philip Treacy

      November 14, 2019 at 2:42 pm

      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

      Reply
  7. Steven Edgar

    October 1, 2019 at 8:14 pm

    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

    Reply
    • Mynda Treacy

      October 3, 2019 at 5:09 pm

      Thanks Steven, glad this was useful for you

      Reply
  8. jason

    October 18, 2017 at 1:04 am

    Thanks 🙂

    Reply
    • Philip Treacy

      October 18, 2017 at 9:13 am

      You’re welcome.

      Reply
  9. BKNelson

    April 14, 2017 at 3:03 am

    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.)

    Reply
    • Catalin Bombea

      April 14, 2017 at 3:27 am

      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

      Reply
    • Philip Treacy

      April 14, 2017 at 8:23 am

      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

      Reply
    • Orville

      April 25, 2018 at 8:32 am

      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

      Reply
  10. S K LOHAR

    May 18, 2013 at 8:20 pm

    thanx a lot, it works.

    Reply
    • Mynda Treacy

      May 19, 2013 at 7:52 pm

      You’re welcome, SK 🙂

      Reply
  11. Colleen

    December 5, 2012 at 8:12 am

    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!

    Reply
    • Mynda Treacy

      December 5, 2012 at 8:35 am

      Glad I could help, Colleen 🙂

      Reply
  12. Greg

    November 12, 2012 at 2:39 am

    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.

    Reply
    • Mynda Treacy

      November 12, 2012 at 6:49 am

      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.

      Reply
      • Mark Bishop

        November 18, 2016 at 11:44 am

        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

        Reply
        • Mynda Treacy

          November 21, 2016 at 9:45 am

          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

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x