Picking Worksheets Quickly

When you right click on one of the navigation arrows, It shows a menu with a partial list of worksheet (tab) names which is kind of helpful, but not groundbreaking.

If you click on the [More Sheets…] option, you get a different dialog with the full list of worksheet names. Any worksheet name can be selected to activate that worksheet.

Even better, if you know the first letter of the worksheet name, you can type that letter and the selection jumps to a worksheet that starts with the letter.
Each time you type the letter, the selection moves to the next worksheet name that starts with that letter.

If your spreadsheet has a separate worksheet for each of the states, you could type a "K" and jump straight to "Kansas"!

Roger Barnes:
Store your macro in your Personal Workbook and you can then use it in any workbook you choose to open. No need to open a second workbook.

I have used the hyperlink table of contents approach, each sheet being an apartment,and it's very efficient. I add a hyperlink back to the master list. Two clicks takes you to any sheet. Fast and efficient once set up and not difficult or time-consuming to set up even with more than 100 sheets.

This is a great tip. Perfect if you have 16 sheets or less. It is a bit of a pain to have to click on more sheets after scanning the displayed ones to find the one you are after is under "more sheets". Especially this is the case if you have a multitude more than 16. I followed the hyperlink tip from Lorenz and I find it is the best tip for workbooks with a large number of worksheets. If you situate your hyperlink worksheet at the beginning, then all you need to do his hit the "beginning" control arrow and immediately click on the "hyperlink worksheet" and there you have the complete list of sheets all before your eyes. Especially if you have 50 or 60 sheets for example, they can be displayed in several columns, in alphabetical order. The other best thing about this method is that you can go to the desired worksheet and be at your desired position in the sheet when you click the hyperlink.Thankyou Lorenz.

This is not that useful as it still doesn't show all my worksheet tabs and I have to click on More Sheets.

Wonderful tip Allen. I never knew about this.

David S your Page Up and Page Down is good too but I think the point here was for workbooks with many worksheets where all tabs might not be visible. If you're at the 10th tab and don't know if the stuff you're looking for is on the 3rd tab or the 22nd tab, you'd be Paging Up or Down, or clicking or scrolling aimlessly.

Thanks Lorenz for the letting me know about the Hyperlink tip. I have used Hyperlink's before but never gave it a thought, I will try this method.
Thank you!!

Perhaps this has already been covered, since I am new to this forum, and this sort-of relates to the above conversation, but if you're a keyboard oriented person like I am, Ctrl+Page Up or Ctrl+Page Down will move you back one sheet or forward one sheet, as the case may be. It's quick if you have several sheets you are working with.

I created a short macro that creates a list of all the worksheets in the active workbook and let's you choose which one to go to. I store it in my "macro" workbook so my main workbooks remain macro free and I keep only one copy of it. I just need to have both files open at the same time.

Hi Hugh,
Aside from creating a button and assigning a macro to it, you can also use "Hyperlink" to navigate through your workbook. This will make your workbook "macro-free".
1. In your "Main Menu" sheet, right-click any blank cell, and choose Hyperlink.
The Insert Hyperlink dialog box will appear.
2. On the Link To: panel at the left, click "Place in this Document".
In the middle of the dialog box, scroll up and find "Cell Reference" (the list below this are the tabs/worksheets in your workbook)
3. Select the desired tab where you want to navigate when you click the cell (note: the sheet name automatically appears on the "Text to display:" box. This is the text that will be displayed on the cell. You can replace this, as you desired.
4. In the "Type the cell reference:" box, type the desired cell reference where you want your cursor to "land" on the selected worksheet (default is A1)
5. On the top-right side of the dialog box is the "ScreenTip" button. Clicking this button will display the Set Hyperlink ScreenTip dialog box where you can type the text that you want to be displayed when you hover your mouse on the cell with Hyperlink (the default is a "garbage" text showing the path/address of the hyperlink).
Click OK when done
6. Click Ok to close the Insert Hyperlink dialog box.
7. Repeat the process for all the other sheets

This is a great tip!!! When I have a lot of worksheets I make up a "Main Menu" with buttons and assign a macro to each button. With this tip I can just right click and boom I am there.

If your workbook has more than 16 worksheets, the last entry in the pop-up list is "More Sheets...". When you click on it, an Activate dialog box opens with a scrollable list of all the sheets in the workbook. Double-click the name of the worksheet you want, and it moves into view on the tab bar and opens as the active sheet.