Microsoft Office Tutorials and References
In Depth Information
Sorting Worksheets
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub SortSheets()¶
'Variable declarations¶
Dim FirstSheet As Long, LastSheet As Long, Counter As Long¶
Dim i As Long, j As Long¶
Dim Ascending As Boolean¶
Dim ActiveWS As Object¶
'Change this variable if descending order is wanted¶
'sets order programatically¶
Ascending = True¶
'sets order via user input in worksheet¶
Ascending = Range("B1").Value¶
'Make sure a workbook is open¶
If ActiveWorkbook Is Nothing Then¶
MsgBox "This macro must be run on a visible workbook", _¶
vbCritical¶
Exit Sub¶
End If¶
'Number of sheets that are selected¶
Counter = ActiveWindow.SelectedSheets.Count¶
If Counter = 1 Then¶
FirstSheet = 1¶
LastSheet = ActiveWorkbook.Sheets.Count¶
Else¶
FirstSheet = ActiveWindow.SelectedSheets(1).Index¶
LastSheet = ActiveWindow.SelectedSheets(Counter).Index¶
If LastSheet - FirstSheet + 1 <> Counter Then¶
MsgBox Prompt:="This requires contiguous sheets to run", _¶
Buttons:=vbCritical¶
Exit Sub¶
End If¶
End If¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
Set ActiveWS = ActiveSheet¶
'Do what is called a “bubble sort” routine¶
For i = FirstSheet To LastSheet - 1¶
For j = i + 1 To LastSheet¶
If Ascending Then¶
'Check both names¶
If LCase$(ActiveWorkbook.Sheets(i).Name) > _¶
LCase$(ActiveWorkbook.Sheets(j).Name) Then¶
'Need to move it¶
ActiveWorkbook.Sheets( _¶
j).Move Before:=ActiveWorkbook.Sheets(i)¶
End If¶
Exl
Search JabSto ::




Custom Search