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.
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
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:
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:
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information ...
Discover MoreWant to grab the names of all the worksheets in a workbook? Here's how you can stuff all those names into the cells of a ...
Discover MoreExcel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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 © 2024 Sharon Parq Associates, Inc.
Comments