Reorder Hidden Sheets

[white84]

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!

 

[Jeffrey Mahoney]

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

'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

 

[Jeffrey Mahoney]

When you see the list of Hidden sheets, they are listed in the order from left to right.

 

[white84]

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 2^ndmacro 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.

 

[Jeffrey Mahoney]

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

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

 

[My Aswer Is This]

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

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

 

[white84]

Thank you all very much for your assistance!

 

[white84]

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

'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!

 

[Jeffrey Mahoney]

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

 

[white84]

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.