Order of hidden tabs

[RodneyC]

I've inherited a number of files, some have a lot of hidden tabs, in some cases as many as 20. I need to unhide these one at a time, add new data, then re-hide. When I look to find the needed hidden tab, I see all 20 but they appear to be in random order. Is there anything I can do to control the order? I'd really like to put them in alphabetical order.

Thanks in advance

 

[Jeffrey Mahoney]

This code will sort the hidden sheets. It requires an empty sheet named SortSht to be created. It requires the sheets to be hidden.

Sub SortHiddenWS()
  Dim Sht As Worksheet
  Dim Cel As Range
  Dim R As Range
  Dim SortSht As Worksheet
  Dim X As Long
  Dim ShtCnt As Long
  Dim Astr As String
  
  Set SortSht = Sheets("SortSht")
  SortSht.Range("A:A").ClearContents
  Set R = SortSht.Range("A1")
  
  X = -1
  For Each Sht In ThisWorkbook.Worksheets
    If Sht.Visible = xlSheetHidden Then
      X = X + 1
      R.Offset(X, 0).Value = Sht.Name
    End If
  Next Sht
  
  Set R = SortSht.Range(R, R.Offset(10000, 0).End(xlUp))
  
  SortSht.Sort.SortFields.Clear
  SortSht.Sort.SortFields.Add2 Key:=R, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  With SortSht.Sort
    .SetRange R
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  ShtCnt = ThisWorkbook.Worksheets.Count
  
  For Each Cel In R
    Astr = Cel.Value
    Sheets(Astr).Move After:=Sheets(ShtCnt)
  Next Cel
  

End Sub

 

[RodneyC]

This code will sort the hidden sheets. It requires an empty sheet named SortSht to be created. It requires the sheets to be hidden.

Sub SortHiddenWS()
  Dim Sht As Worksheet
  Dim Cel As Range
  Dim R As Range
  Dim SortSht As Worksheet
  Dim X As Long
  Dim ShtCnt As Long
  Dim Astr As String
 
  Set SortSht = Sheets("SortSht")
  SortSht.Range("A:A").ClearContents
  Set R = SortSht.Range("A1")
 
  X = -1
  For Each Sht In ThisWorkbook.Worksheets
    If Sht.Visible = xlSheetHidden Then
      X = X + 1
      R.Offset(X, 0).Value = Sht.Name
    End If
  Next Sht
 
  Set R = SortSht.Range(R, R.Offset(10000, 0).End(xlUp))
 
  SortSht.Sort.SortFields.Clear
  SortSht.Sort.SortFields.Add2 Key:=R, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  With SortSht.Sort
    .SetRange R
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
 
  ShtCnt = ThisWorkbook.Worksheets.Count
 
  For Each Cel In R
    Astr = Cel.Value
    Sheets(Astr).Move After:=Sheets(ShtCnt)
  Next Cel
 

End Sub

Thanks. This looks above my current skill set and this task is low priority. I'll save this and attempt in the next week. THANK YOU

 

[Peter_SSs]

This will sort any hidden sheets* in alphabetical order and place them all after the visible sheet(s). The visible sheets will remain in the order that they currently are (but they could be sorted too if you want). No extra sheet is required.

* I have assumed that the hidden sheets are not xlVeryHidden

Sub SortHiddenSheets()
  Dim AL As Object
  Dim ws As Worksheet
  Dim itm As Variant

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each ws In Worksheets
    If Not ws.Visible Then
      AL.Add ws.Name
    End If
  Next ws
  If AL.Count > 0 Then
    AL.Sort
    AL.Reverse
    For Each itm In AL.ToArray
      Worksheets(itm).Move After:=Worksheets(Worksheets.Count)
    Next itm
  End If
End Sub