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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Jeffrey Mahoney

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

ADVERTISEMENT

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,855
Office Version
  1. 2013
Platform
  1. 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

ADVERTISEMENT

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,768
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,109,348
Messages
5,528,187
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top