Forum Discussion
Scrolling through hidden worksheets
It seems like you are experiencing an issue with the default behavior of Excel when scrolling through hidden worksheets using the keyboard shortcut. Unfortunately, Excel does not provide a built-in feature to scroll through hidden worksheets with the same first letter.
Note: In Excel 2016 and earlier versions, pressing Ctrl + Page Down does not directly display the "Activate" dialog box. This feature is available in Excel 2019 and Excel 365.
However, you can try the following workaround to filter through hidden worksheets more efficiently:
- Press Ctrl + Page Down to display the "Activate" dialog box.
- Type the first letter of the sheet name you are looking for.
- Press Enter to activate the first sheet that matches the criteria.
- If the desired sheet is not activated, press Ctrl + Page Down again to show the "Activate" dialog box and repeat the process.
This method allows you to quickly navigate through the hidden worksheets by typing the first letter of their names.
Alternatively, you can use VBA (Visual Basic for Applications) to create a custom solution that enables you to scroll through hidden worksheets more smoothly.
Here is a simple VBA code that achieves this:
Vba Code is untested and is a sample; please backup your file first.
Sub ScrollThroughHiddenSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetHidden Then
ws.Activate
Exit Sub
End If
Next ws
End SubTo use this code:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above VBA code into the module window.
- Close the VBA editor.
- Press Alt + F8 to open the "Macro" dialog box.
- Select ScrollThroughHiddenSheets from the list and click Run.
This macro will activate the first hidden worksheet it encounters. You can assign this macro to a keyboard shortcut for quick access.
Please note that using VBA macros requires enabling macros in your Excel settings, and it's recommended to save your workbook as a macro-enabled (.xlsm) file format. The text, steps and the code were created with the help of AI
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
"if there is another solution that does nor require me to enable macros"
The apparent answer is that a macro is required.
But you can execute a macro without "enabling macros" if the macro is contained in your Personal Macro Workbook — Personal.xlsb, in modern versions of Excel. Macros stored there are always enabled. And once created, this workbook an its macros are available to you (mostly hidden) every time you open your desktop version of Excel.
And the macro can use a user-defined form (UserForm) to receive your input and unhide the desired worksheet(s).See the attached workbook for more information.
I'm facing the same problem. Up until a while ago, I was able to "filter" through hidden sheets by using first letter of a sheet in the Hidden Sheets Dialog Box.
Example: sheet names A_1, A_2, A_3 and so on. If I kept pressing "A", it would go from 1st to 2nd to 3rd sheet starting with the letter "A". Now, nothing happens anymore, it stops after finding the first match.
It was an easy job to find whatever hidden sheet I needed. Why is a macro required all of a sudden?
"Why is a macro required all of a sudden?"
I have no idea why. Like almost all of the Tech Community members, I am not a Microsoft employee, much less one who works on the Excel products.
Remember that you can let Microsoft know your feelings from within Excel—use the Feedback button under the Help menu.
