How to put worksheets in Alphabetical order?

C

Chip Pearson

Open the VBA editor by pressing ALT F11. In the editor, select your
workbook's project in the "tree view" on the left side of the screen (CTLR R
to view it if it is hidden). Then go to the Insert menu and choose Module.
In that code window, paste the code from www.cpearson.com/Excel/SortWB.aspx.

Once you have the code in the module, close the editor from the File menu to
return to Excel. There, press ALT F8 to display the available VBA
procedures, select the item and click RUN.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
L

LiveUser

Chip,

Do I enter all of this in just like this?
and thank you for responding.

Attribute VB_Name = "modSortingWorksheets"
Option Explicit
Option Compare Text


Public Function SortWorksheetsByName(ByVal FirstToSort As Long, ByVal
LastToSort As Long, _
ByRef ErrorText As String, Optional ByVal SortDescending As Boolean =
False) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SortWorksheetsByName
' This sorts the worskheets from FirstToSort to LastToSort by name
' in either ascending (default) or descending order. If successful,
' ErrorText is vbNullString and the function returns True. If
' unsuccessful, ErrorText gets the reason why the function failed
' and the function returns False.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim M As Long
Dim N As Long
Dim WB As Workbook
Dim B As Boolean

Set WB = Worksheets.Parent
ErrorText = vbNullString

If WB.ProtectStructure = True Then
ErrorText = "Workbook is protected."
SortWorksheetsByName = False
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' If First and Last are both 0, sort all sheets.
'''''''''''''''''''''''''''''''''''''''''''''''
If (FirstToSort = 0) And (LastToSort = 0) Then
FirstToSort = 1
LastToSort = WB.Wo.Count
Else
'''''''''''''''''''''''''''''''''''''''
' More than one sheet selected. We
' can sort only if the selected
' sheet are adjacent.
'''''''''''''''''''''''''''''''''''''''
B = TestFirstLastSort(FirstToSort, LastToSort, ErrorText)
If B = False Then
SortWorksheetsByName = False
Exit Function
End If
End If

'''''''''''''''''''''''''''''''''''''''''''''
' Do the sort, essentially a Bubble Sort.
'''''''''''''''''''''''''''''''''''''''''''''
For M = FirstToSort To LastToSort
For N = M To LastToSort
If SortDescending = True Then
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) > 0 Then
WB.Worksheets(N).Move before:=WB.Worksheets(M)
End If
Else
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) < 0 Then
WB.Worksheets(N).Move before:=WB.Worksheets(M)
End If
End If
Next N
Next M

SortWorksheetsByName = True

End Function
 
L

LiveUser

I found another example online that worked fine.

Thank you for you help everyone.

Option Explicit

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then

'Change the 1 to the worksheet you want sorted first
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top