Sort all worksheet contents with a macro

[Scott]

I have searched the boards and have not found what I need. Hoping you can
help. I am using the following to sort worksheet order within a workbook and
it is working perfectly.

http://www.cpearson.com/excel/sortws.htm

What I still haven't found is how to re-sort (by first column) the contents
of all worksheets. Idea situation would be to combine this with the item
referenced above.

Thanks in advance.

Scott

[Tom Ogilvy]

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
Dim sh as Worksheet

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
for each sh in Thisworkbook.Worksheets
sh.UsedRange.Sort Key1:=sh.Range("A1"), _
Order1:=xlAscending, Header:=xlYes
Next
End Sub

Adjust the header setting to fit your data.

--
Regards,
Tom Ogilvy


"Scott" <[email protected]> wrote in message
news:[email protected]...
> I have searched the boards and have not found what I need. Hoping you can
> help. I am using the following to sort worksheet order within a workbook
and
> it is working perfectly.
>
> http://www.cpearson.com/excel/sortws.htm
>
> What I still haven't found is how to re-sort (by first column) the
contents
> of all worksheets. Idea situation would be to combine this with the item
> referenced above.
>
> Thanks in advance.
>
> Scott

[Scott]

Worked like a charm. Thanks Tom!