Reorder Hidden Sheets

white84

New Member
Joined
May 11, 2018
Messages
38
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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,698
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
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,698
When you see the list of Hidden sheets, they are listed in the order from left to right.
 

white84

New Member
Joined
May 11, 2018
Messages
38


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.

 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,698
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
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
 

white84

New Member
Joined
May 11, 2018
Messages
38
Thank you all very much for your assistance!
 

white84

New Member
Joined
May 11, 2018
Messages
38
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!
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,698
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

New Member
Joined
May 11, 2018
Messages
38
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,445
Messages
5,450,490
Members
405,614
Latest member
SJ789

This Week's Hot Topics

Top