Reorder Hidden Sheets

white84

New Member
Joined
May 11, 2018
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
When you see the list of Hidden sheets, they are listed in the order from left to right.
 
Upvote 0


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.

 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top