How do I alphabetize sheets in a large work book?

Answer 1

You can either sort them manually by dragging them, or use some VBA code to do it automatically. To do that, hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. Click Insert > Module, and then paste the following macro in the Module Window.

Sub SortWorkBook()

'Updateby20140624

Dim xResult As VbMsgBoxResult

xTitleId = "KutoolsforExcel"

xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)

For i = 1 To Application.Sheets.Count

For j = 1 To Application.Sheets.Count - 1

If xResult = vbYes Then

If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then

Sheets(j).Move after:=Sheets(j + 1)

End If

ElseIf xResult = vbNo Then

If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then

Application.Sheets(j).Move after:=Application.Sheets(j + 1)

End If

End If

Next

Next

End Sub

You can then close the VBA editor and return to your workbook. Pressing Alt + F8 will open the dialog box from where you can choose to run the macro. It will give you the option of sorthing them in ascending or descending order. The macro is stored in the workbook should you need to run it again.