Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to create buttons to open/go to certain sheets in Excel?

Sometimes, you may need to create a button to open a certain worksheet in current workbook, so how to deal with it? Actually there are several methods that can help you solve it.

Create button to open certain sheet with shape and hyperlink

Create button to open certain sheet with Form control button

Create buttons to open all sheets with Kutools for Excel

Use Kutools for Excel's Navigation Pane to list all opening workbooks and their sheets, click to jump to sheets!

Easily insert multiple macro buttons to go to (each) other worksheets in Excel

For inserting a macro button to go to another worksheet, normally you have to finish all above 5 steps, and it will be quite tedious to insert many macro buttons to go to each worksheet in the current workbook. But, Kutools for Excel’s Create List of Sheet Names utility enables you to quickly insert multiple macro buttons to go to each worksheet. Full Feature Free Trial 60-day!
ad create list of sheet names 1

This Create List of Sheet Names utility also supports to batch insert multiple hyperlinks to go to each worksheet (or other worksheets) in current workbook.

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial


arrow blue right bubbleCreate button to open certain sheet with shape and hyperlink

This method will guide you to insert a rounded rectangle, and then add hyperlink for this rounded rectangle to the certain worksheet. You can do as follows:

1. Click Insert > Shapes > Rounded Rectangle. See screen shot:
doc button open sheets 1

2. Draw a rounded rectangle on the worksheet, and you can format it and type text to it as you need. In our case, we type Go To Sheet1 as below screen shot shown:
doc button open sheets 2

3. Right click the rounded rectangle, and select Hyperlink from the right-clicking menu.
doc button open sheets 3

4. In the opening Insert Hyperlink dialog box,
(1) Select Place in This Document in the Link to section;
(2) Click to select the certain sheet name in the Or Select a place in this document section;
(3) Type the destination cell address into the Type the cell reference box, and at last click the OK button.
doc button open sheets 4

Now when you click the rounded rectangle, it will skip to the specified cell of specified sheet at once.


arrow blue right bubbleCreate button to open certain sheet with Form control button

This method will walk you through creating a form control button to open certain worksheet. You can do as follows:

1. Click the Developer > Insert > Button in Form Controls section. See screen shot:
doc button open sheets 5
Note: By default the Developer tab does not display in Ribbon, click to know how to show/display developer tab in Excel Ribbon.

2. Draw a button, then an Assign Macro dialog box comes out. In the dialog box, type a name for the macro into the Macro name box, and then click the New button.
doc button open sheets 6

3. Now the Microsoft Visual Basic for Applications dialog box opens, please paste following code between sub and End sub. See screen shot:

Code: Go to certain worksheet in Excel

ThisWorkbook.Sheets("Sheet1").Activate

Note: In the code, please change the Sheet1 to required sheet name based on your needs.
doc button open sheets 7

4. Save the code, and close the Microsoft Visual Basic for Application window. Now you will get a form control button as below screen shot shown. Click the button, it will skip to the specified worksheet immediately.


arrow blue right bubbleCreate buttons to open all sheets with Kutools for Excel

Both above methods can only create one button to open one sheet. If you want to batch create multiple buttons to open all worksheets, you should try Kutools for Excel’s Create List of Sheet Names utility.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click the Enterprise > Worksheet > Create List of Sheet Names.
doc button open sheets 01

2. In the Create List of Sheet Names dialog box,
(1) Check the Contains buttons and macros option in the Sheet Index Styles section;
(2) In the Specify sheet name for Sheet Index box, type a name for the new created sheet;
(3) Specify the position of index sheet in the Insert the Sheet index in drop down list;
doc button open sheets 10

3. Click the OK button. Then it will create an index sheet with multiple buttons. Each button is named with its worksheet name and linked to this worksheet. See below screen shot:
doc button open sheets 11

Note: This method requires enabling the Trust to the VBA project object model option in the Trust Center.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: create buttons to open/go to all sheets in Excel

 

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Navigation Pane: List all opening workbooks and vertical sheet tab for easily switching to any sheet

Kutools for Excel’s Navigation Pane lists all opening workbooks and corresponding worksheets vertically as below screenshots. (1) Clicking a workbook in the Workbook section will switch to this workbook window; (2) while clicking a worksheet in the Worksheet section will skip to open this worksheet. Full Feature Free Trial 60-day!
ad navigation pane book sheet 1



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sharthak · 18 days ago
    Is there a way in which once you navigate to a sheet the other sheets are closed?
  • To post as a guest, your comment is unpublished.
    AZEEZ · 6 months ago
    IS THERE ANY OPTION TO MAKE A HYPERLINK IN ALL PAGES SAME TIME TO GO TO THE FIRST SHEET?
  • To post as a guest, your comment is unpublished.
    Steve C · 1 years ago
    Is there a way to make the "cell reference" work with an offset formula?

    For example, every week I add new data in the next column of row 5 and I'd like to navigate to the most recent data. Normally I'd use (OFFSET($B5,0,MAX(0,COUNT($B5:$Z5)-1))). B5 would represent data for the week of 1/6/2018, C5 represents 1/13/2018, D6 is 1/20/2018, and so on. I'd like the button to find the last cell between B5 and Z5 where data was entered.
  • To post as a guest, your comment is unpublished.
    SAQIB · 1 years ago
    Thank you soooooooooooooo much it is very easy and helpful for me
  • To post as a guest, your comment is unpublished.
    Sandeep · 2 years ago
    HI,
    Thank you so much !!!. Very helpful !!! :-)