Sorting worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know a method to sort worksheets
alphabetically within a workbook? I'm sick of having to
manually move workseets to get them in the order I want.
 
The following macro will do what you want

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
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
 
Posted previously by one of the other guys:-

Sub SortALLSheets()
'modification of coded example by Bill Manville
Dim iSheet As Integer, iBefore As Integer
For iSheet = 1 To ActiveWorkbook.Sheets.Count
Sheets(iSheet).Visible = True
For iBefore = 1 To iSheet - 1
If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then
ActiveWorkbook.Sheets(iSheet).Move
Before:=ActiveWorkbook.Sheets(iBefore)
Exit For
End If
Next iBefore
Next iSheet
End Sub '-- SortALLSheets()
 
Thanks a ton!
-----Original Message-----
Posted previously by one of the other guys:-

Sub SortALLSheets()
'modification of coded example by Bill Manville
Dim iSheet As Integer, iBefore As Integer
For iSheet = 1 To ActiveWorkbook.Sheets.Count
Sheets(iSheet).Visible = True
For iBefore = 1 To iSheet - 1
If UCase(Sheets(iBefore).Name) > UCase(Sheets (iSheet).Name) Then
ActiveWorkbook.Sheets(iSheet).Move
Before:=ActiveWorkbook.Sheets(iBefore)
Exit For
End If
Next iBefore
Next iSheet
End Sub '-- SortALLSheets()

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

---------------------------------------------------------- ------------------
Attitude - A little thing that makes a BIG difference
---------------------------------------------------------- ------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003


.
Click to expand...
 
Thanks a ton!
-----Original Message-----
The following macro will do what you want

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
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




.
Click to expand...
 
Back
Top