Sorting tabs by alpha

[sdspidy]

I have a large workbook with 50+ worksheets of physician names that I want to put in alphabetical order to make it easier to find each tab as needed. Is there a way to do that beyond manually dragging the tabs?

Thank you!

 

[JoeMo]

Here's a macro you can try.

Sub SortSheets()
'Purpose is to sort the sheets in a workbook in either ascending or descending order
'according to the sheet tabs, EXCEPT, IF THERE IS AN 'Index' SHEET, IT WILL BE
'PLACED FIRST (LEFTMOST SHEET TAB).

Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
Dim lReply As Long

Application.ScreenUpdating = False
lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _
& "To sort Worksheets descending select 'No'", vbYesNoCancel, "Sheet Sort")
If lReply = vbCancel Then Exit Sub

lShtLast = Sheets.Count

    If lReply = vbYes Then 'Sort ascending
        For lCount = 1 To lShtLast
            For lCount2 = lCount To lShtLast
                If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
                    Sheets(lCount2).Move before:=Sheets(lCount)
                End If
            Next lCount2
        Next lCount
    Else 'Sort descending
     For lCount = 1 To lShtLast
            For lCount2 = lCount To lShtLast
                If UCase(Sheets(lCount2).Name) > UCase(Sheets(lCount).Name) Then
                    Sheets(lCount2).Move before:=Sheets(lCount)
                End If
            Next lCount2
        Next lCount
    End If
    
'All sheets are sorted either ascending or decending, but want 'Index' sheet to be first

        On Error Resume Next 'in case there is no Index sheet
        Sheets("Index").Move before:=Sheets(1)

End Sub

 

[Rick Rothstein]

I have a large workbook with 50+ worksheets of physician names that I want to put in alphabetical order to make it easier to find each tab as needed. Is there a way to do that beyond manually dragging the tabs?

Do all of your tabs contain physician names or do you have some "generic" (non-physician) tabs that should not moved? If you have generic tabs, are they located in front of the physician tabs? If so, how many "generic" tabs do you have?

 

[Rick Rothstein]

Do all of your tabs contain physician names or do you have some "generic" (non-physician) tabs that should not moved? If you have generic tabs, are they located in front of the physician tabs? If so, how many "generic" tabs do you have?

Okay, here is a routine that you can use and adjust for any generic worksheets you may have as long as those generic sheet are located before any physician sheets.

Sub AlphabetizeTabs()
  Dim X As Long, TabNames As Variant
  With CreateObject("System.Collections.ArrayList")
    For X = 1 To Sheets.Count
      .Add Sheets(X).Name
    Next
    .Sort
    .Reverse
    TabNames = .ToArray
    Application.ScreenUpdating = False
    For X = 1 To UBound(TabNames)
      Sheets(TabNames(X)).Move Before:=Sheets([B][COLOR="#FF0000"][SIZE=3]1[/SIZE][/COLOR][/B])
    Next
    Application.ScreenUpdating = True
  End With
End Sub

Note 1: If you have generic tab sheets, then change the red highlighted 1 to the position number of the first physician's tab counting from the left.

Note 2: The above code sorts the list in ascending order... if you want the list sorted in descending order, then comment out the .Reverse code line.

 

[sdspidy]

I wish I was that talented at Excel. I don't have a clue how to do macros like this. Thank you for posting though.

 

[sdspidy]

The tabs are named by the physicians last name and then initials if needed after to avoid erroneous sorting. The are no generic tabs.

 

[Rick Rothstein]

The tabs are named by the physicians last name and then initials if needed after to avoid erroneous sorting. The are no generic tabs.

Did you see what I posted in Message #4 yet?

 

[sdspidy]

I don't know how to run macros.

 

[Rick Rothstein]

I don't know how to run macros.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the posted code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[sdspidy]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the posted code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you Rick! I got it to work and learned a few new tricks in the process.

Have a great day!