Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Reorder Hidden Sheets

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Reorder Hidden Sheets

    I have inherited a file with 200+ hidden sheets, linked and summarized on a "Summary" tab. Every so often I need to unhide 1 or 2 sheets to review, and then re-hide. My problem is, when I right click to un-hide the list is not in numerical order. Does anyone know how to reorder this list? Thanks in advance!

  2. #2
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    You can use the two macros below. ListSheetNames creates a list of all the sheets in the current workbook. You then can sort the list any way you want. Then highlight all the sheet names and run the second macro named called SortSheets.

    Jeff

    Code:
    'Put your cursor on an empty column before running this macro
    Sub ListSheetNames()
      Dim Sht As Worksheet
      Dim X As Long
      Dim Cel As Range
      
      Set Cel = ActiveCell
      X = 0
      For Each Sht In ThisWorkbook.Worksheets
        X = X + 1
        Cel.Offset(X, 0).Value = Sht.Name
      Next Sht
      
    End Sub
    
    
    'Highlight the list of sheets before running this macro
    Sub SortSheets()
      Dim Cel As Range
      Dim Sht As Worksheet
      Dim X As Long
      Dim ShtCnt As Long
      
      X = 0
      For Each Sht In ThisWorkbook.Worksheets
        X = X + 1
      Next Sht
      ShtCnt = X
      
      For Each Cel In Selection
        Set Sht = Sheets(Cel.Value)
        Sht.Move after:=Sheets(ShtCnt)
      Next Cel
      
      
    End Sub
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  3. #3
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    When you see the list of Hidden sheets, they are listed in the order from left to right.
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  4. #4
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets



    I am makingprogress here, thank you!

    When I try to sort the sheet names I have another hiccup. Instead of ordering


    Sheet1

    Sheet2

    Sheet3

    Etc



    They aresorting as

    Sheet1

    Sheet10

    Sheet11

    Etc



    I did afind/replace “Sheet” with “”, and sorted again. The list is in the correctnumerical order now. Then I applied custom formatting as “Sheet”###. But while the cell appears to match the tab name, thismaintains only the numerical value in each cell, so when I run the 2ndmacro it isn’t sorting correctly.





    Can anyonepoint my in the right direct to get the values in each cell to match the sheetnames again? Seems like a simple formatting/sorting issue I am overlooking oroverthinking.


  5. #5
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    Alphanumeric sorting is a issue in Excel. The best thing to do is rename the sheets so the formatting is constant. This will rename all the sheets that contain "Sheet". Example:
    Sheet1 will become Sheet001
    Sheet11 wil become Sheet 011


    Code:
    Sub RenameSheets()
      Dim Sht As Worksheet
      Dim N As Long
      Dim A As String
      Dim B As String
      
      For Each Sht In ThisWorkbook.Worksheets
        A = Sht.Name
        If InStr(A, "Sheet") > 0 Then
          N = Val(Mid(A, 6, 100))
          B = "Sheet" & Format(N, "000")
          Sht.Name = B
        End If
      Next Sht
          
    End Sub
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,338
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Reorder Hidden Sheets

    Maybe you would like this solution:

    Enter a sheet name into Range("A1") or Range("A2")
    Then if you double click on the sheet name the Sheet will toggle from visible to not visible

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified  3/6/2019  11:05:43 AM  EST
    If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
    Cancel = True
    Dim ans As String
    ans = Target.Value
    On Error GoTo M
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
        Sheets(ans).Visible = Not Sheets(ans).Visible = True
    End If
    Exit Sub
    M:
    MsgBox "Sheets  " & ans & " Does not Exist"
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    Thank you all very much for your assistance!

  8. #8
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    Quote Originally Posted by Jeffrey Mahoney View Post
    You can use the two macros below. ListSheetNames creates a list of all the sheets in the current workbook. You then can sort the list any way you want. Then highlight all the sheet names and run the second macro named called SortSheets.

    Jeff

    Code:
    'Put your cursor on an empty column before running this macro
    Sub ListSheetNames()
      Dim Sht As Worksheet
      Dim X As Long
      Dim Cel As Range
      
      Set Cel = ActiveCell
      X = 0
      For Each Sht In ThisWorkbook.Worksheets
        X = X + 1
        Cel.Offset(X, 0).Value = Sht.Name
      Next Sht
      
    End Sub
    
    
    'Highlight the list of sheets before running this macro
    Sub SortSheets()
      Dim Cel As Range
      Dim Sht As Worksheet
      Dim X As Long
      Dim ShtCnt As Long
      
      X = 0
      For Each Sht In ThisWorkbook.Worksheets
        X = X + 1
      Next Sht
      ShtCnt = X
      
      For Each Cel In Selection
        Set Sht = Sheets(Cel.Value)
        Sht.Move after:=Sheets(ShtCnt)
      Next Cel
      
      
    End Sub
    Jeff,
    I have added a few more sheets to my file this month and I have added them to the alpha-numeric list I created, but I am experiencing 2 issues this time.
    First, all fields with "General" formatting which contain a number have been converted to a "Date" format. Any idea what is happening here?
    Second, I receive an error when running the Macro "Subscript out of range". I have highlighted the full list, do you know what could be causing this error?

    Thanks in advance!

  9. #9
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    First, all fields with "General" formatting which contain a number have been converted to a "Date" format. Any idea what is happening here?
    These macros don't change any cell formatting

    Second, I receive an error when running the Macro "Subscript out of range". I have highlighted the full list, do you know what could be causing this error?
    You may get this error if any of the sheets in your list do not exists. Did you run the first macro "ListSheetNames"?
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  10. #10
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    I had not run the first macro, but when I just did it worked. I sorted my list and still received the same error as mentioned earlier.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •