sort wrork sheets

D

DAN T

i need to sort some work sheets by alpha, i know how to sort on a work
sheet but not how to sort several work sheets in a file
 
D

Don Guillett

try these

Sub SortALLSheetsbyName() 'McRitchie
'sort sheets within a workbook in Excel 7 -- Bill Manville
'modified to sort all sheets instead of just worksheets
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
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

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

Sub ss()
ReDim SheetsArray(Sheets.Count)
For x = 1 To Sheets.Count
SheetsArray(x) = Sheets(x).Name
Next x
' do sort of the array here
For x = 1 To Sheets.Count
If Sheets(x).Name <> SheetsArray(x) Then
Sheets(SheetsArray(x)).Move Before:=Sheets(x + 1)
End If
Next x
End Sub
 
Top