Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Jumping to a Specific Worksheet.

Jumping to a Specific Worksheet

Written by Allen Wyatt (last updated May 4, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

If you have a huge number of worksheets in a workbook, you may be looking for a way to jump to a specific sheet rather easily. There are a number of ways you can approach this task, and their applicability to your situation depends on how many worksheets you actually have in the workbook.

One option that works well if you have a limited number of worksheets (say, 30-40 sheets or less) is to right-click the sheet navigation buttons at the left of the sheet tabs. Doing so will pull up a list of worksheet names, and you can select which one you want to jump to. If there are more worksheets than can comfortably fit in the list, then one of the options is "More Sheets." Select that option, and you end up with a dialog box that lists all the worksheets and you can make your selection.

Another option that many people employ is to create a "table of contents" for your workbook. In the first worksheet, enter a bunch of hyperlinks that jump to the various worksheets in your workbook. That way you can display the TOC, click a link, and you are on your way.

If you know the name of the worksheet you want to jump to, you can also use the Go To capabilities of Excel. Follow these steps:

  1. Press F5. Excel displays the Go To dialog box.
  2. In the Reference box, enter Sheet83!A1. (Replace "Sheet83" with the name of the worksheet you want to jump to.)
  3. Click OK.

Another option is to create a macro to prompt for either the name or number of the worksheet you want to display. The following macro could be assigned to a shortcut key, and then you can use it to jump to whatever sheet is desired.

Sub GotoSheet()
    Dim sSheet As String

    sSheet = InputBox( _
      Prompt:="Sheet name or number?", _
      Title:="Input Sheet")
    On Error Resume Next
    If Val(sSheet) > 0 Then
        Worksheets(Val(sSheet)).Activate
    Else
        Worksheets(sSheet).Activate
    End If
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7094) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Jumping to a Specific Worksheet.

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

Specifying a Paper Tray in a Macro

If you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a ...

Discover More

Reducing File Size

As you work with a workbook (particularly one that contains macros) you may notice that the workbook size can become ...

Discover More

Printing a List of AutoCorrect Entries

Want a printed record of the AutoCorrect entries you've created in Word? There is no built-in way to do it, but you can ...

Discover More

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!

More ExcelTips (ribbon)

Testing for an Empty Worksheet

If you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. ...

Discover More

Dynamic Worksheet Tab Names

Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but ...

Discover More

Copying a Single Cell to Multiple Worksheets

If you need to copy a cell from one worksheet to a bunch of other worksheets, the work can quickly get tedious. Make the ...

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

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 8 - 6?

2024-05-05 16:02:59

J. Woolley

Sam Gardner's macro works fine but it ignores any chart sheets and it must be repeated if worksheets are added, deleted, moved, or renamed. Hidden worksheets are included but their hyperlink does nothing.
My Excel Toolbox includes the following dynamic array function:
    =ListSheets([SkipHidden], [SkipHeader])
This function returns one row with sheet names in subsequent columns. To return the list in one column with several rows, use this:
    =TRANSPOSE(ListSheets(...))
After populating a column with sheet names, you can create a hyperlink to each sheet by adding a formula to the next column; however, there are certain issues when a hyperlink is created using the HYPERLINK function:
+ A hyperlink cannot reference a worksheet without identifying a cell, range, or name on that sheet
+ Activating such a hyperlink will change the worksheet’s previously selected cell or range
+ A hyperlink cannot reference a chart sheet because a chart sheet has no cells
+ A hyperlink referencing a hidden sheet will fail silently when activated
All of these issues are resolved by the following My Excel Toolbox function:
    =SheetNameLink(Sheet_Name, [Friendly_Name], [Screen_Tip])
For example, if cells A2:A20 contain a list of sheets created by the ListSheets function described above, the following formula in cell B2 would create a hyperlink to the worksheet or chart sheet listed in cell A2:
    =SheetNameLink(A2)
This formula can be duplicated in B2:B20. The ListSheets and SheetNameLink functions will recalculate (F9) if a sheet is added, deleted, moved, or renamed.
My Excel Toolbox also includes the following function:
    =SheetListUpdateLink([Friendly_Name], [Screen_Tip])
This function uses SuperLink to create a hyperlink that will list a workbook's sheets (including hidden sheets) in subsequent rows. Each sheet in the list includes a hyperlink to hide or activate (unhide) the sheet. For more information, review the PDF file UseSheetList.pdf.
The SheetsDialog macro (Ctrl+T S H) in My Excel Toolbox displays a sheet activation list at the bottom-left corner of Excel's window. This is similar to right-clicking a navigation button at the left of the sheet tabs (see the Tip's second paragraph). Here is an abbreviated version:

Sub SheetsDialog()
    Dim X, Y
    Const PxPerPt = 96 / 72
    With Application
        X = .Left * PxPerPt
        Y = (.Top + .Height) * PxPerPt
        .CommandBars("Workbook tabs").ShowPopup X, Y
    End With
End Sub

Finally, Ctrl+PageDown and Ctrl+PageUp are shortcuts to move from sheet to sheet.
See https://sites.google.com/view/MyExcelToolbox/
and https://excelribbon.tips.net/T011679_Retrieving_Worksheet_Names.html


2024-05-04 13:54:31

Sam Gardner

This "Jump To" and Add a Table of Contents function would be awfully clunky if you had many sheets. What you really want is a macro that will create your TOC for you. The macro below will do that, titling the Table of Contents as sheet "TOC9999", where the digits are randomized between 1 and 1000. This means if you run it multiple times (which you're likely to do, if you have a workbook with dozens of sheets you'll probably be adding more) you're unlikely to get an error based on already having an existing sheet name. Then you can just adjust the initial sheet.

Sub AddTOC()
'
' AddTOC Macro

Dim WS As Worksheet
Dim WS_Count As Integer
Dim I As Integer
Dim strTOCName

Sheets.Add Before:=Sheets(1)
'Okay, I hate the clunkiness of checking for the sheet first. I'm just going to add a random number to the name
'It's lazy, but I can pretty much just delete any TOC's I don't want or remove the random number in the name
'if I'm running it multiple times or updating the TOC

upperBound = 1000
lowerBound = 1
strRandom = Int(2 + Rnd * (upperBound - lowerBound + 1))
''

strTOCName = "TOC" & strRandom
Sheets(1).Name = strTOCName

Sheets(strTOCName).Range("A1").Select
ActiveCell.Value = "Table of Contents"
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 2 To WS_Count
strName = Worksheets(I).Name
ActiveCell.Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
strName & "!A1", TextToDisplay:=strName

Next I


End Sub


2024-05-04 05:43:56

Alex Blakenburg

One thing that is seldom mentioned is that, when you right-click the sheet navigation buttons you can start typing the start of the sheet name and the cursor will jump to the sheet name in the list that starts with those characters. If you can type fast enough I have managed to 5 characters to work which works well if your sheets names are say Account Numbers or Cost Centre numbers. If you have multiple sheets starting with a similar prefix then each time you type those characters it will jump to the next sheet name in the list starting with those characters.


This Site

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.

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.