Need a Macro to alphabetize Spreadsheet names

Posted by Deborah Morera on September 03, 2001 8:36 PM

I was wondering if anyone out there would be able to tll me what the code is for alpabetizing the names of spreadsheets. I have a workbook with a spreadsheet for each client. I would like to alphabetize the spreadsheets each time I add a new client. Also, would it be too much of a pain to alphabetize all but two spreadsheets in a workbook? I have two spreadsheets that do not have client names. They are my billing reports, and I would like those to remain at the end of the workbook (not be alphabetized).

Thanks in advance!

Posted by Ivan F Moala on September 04, 2001 12:36 AM

Try something like this

Just change the sheet names you don't want sorted
in this routine.
In this case they are "Hertory" & "aReport"
The routine sorts in ascending order to change
to descending order then change the variable
SortDescending to True

To place this code in the VBE
1) Alt F11
2) Ctrl R
3) select the project name/workbook
4) right click and select insert | module
5) paste this code

Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

FirstWSToSort = 1
LastWSToSort = Worksheets.Count

Application.ScreenUpdating = False

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
If Worksheets(N).Name &LT;&GT; "Hertory" Or Worksheets(N).Name &LT;&GT; "aReport" Then
Worksheets(N).Move before:=Worksheets(M)
End If
End If
Else
If UCase(Worksheets(N).Name) &LT; UCase(Worksheets(M).Name) Then
If Worksheets(N).Name &LT;&GT; "Hertory" Or Worksheets(N).Name &LT;&GT; "aReport" Then
Worksheets(N).Move before:=Worksheets(M)
End If
End If
End If
Next N
Next M

Sheets("Hertory").Move after:=Worksheets(Worksheets.Count)
Sheets("aReport").Move after:=Worksheets(Worksheets.Count)

End Sub