Forum Discussion

Maxbeau98's avatar
Maxbeau98
Copper Contributor
May 23, 2024

Scrolling through hidden worksheets

Hi, 

 

I have a workbook which has many hidden worksheets (roughly 100).

Until recently if I went to unhide the worksheets, I could find the one I was looking for by typing the first letter of the name of the sheet, and then it would automatically scroll through the sheets with the corresponding first letter. 

 

Now if I do this, it only goes to the first sheet with the name starting with the letter, but doesn't scroll through to the other ones. 

 

Does anyone have an easy solution to filter through the hidden worksheets? 

excel

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    May 23, 2024

    Maxbeau98 

    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:

    1. Press Ctrl + Page Down to display the "Activate" dialog box.
    2. Type the first letter of the sheet name you are looking for.
    3. Press Enter to activate the first sheet that matches the criteria.
    4. 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 Sub

    To use this code:

    1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    2. Go to Insert > Module to insert a new module.
    3. Copy and paste the above VBA code into the module window.
    4. Close the VBA editor.
    5. Press Alt + F8 to open the "Macro" dialog box.
    6. 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.

    • cbreahg's avatar
      cbreahg
      Copper Contributor
      Jun 14, 2024
      I have that same issue since yesterday. I used to be able to find any hidden sheet by typing the first letter of the name of that sheet multiple times until I reached the correct sheet. Now, I can only get to the first sheet with that letter. I would like to know if there is another solution that does nor require me to enable macros
      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Jun 16, 2024

        cbreahg 

        "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.

Resources