sorting in multiple sheets
 

Hi Tom,

i have multiple sheets from A to Z
and i need to sort column L
i used this code

Sub sort()
cells.sort Key1:=range("L2"), Header:=xlYes
End Sub

it's working for the single sheet.
but i want it for all the sheets
how to modify your code.i tried but could't get thru.

thanks in advance
Ren

"Tom Ogilvy" wrote:

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 Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=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

sorting in multiple sheets
 

If it is all sheets in workbook then use this.....

Sub sort()
Dim wS as Worksheet
For Each wS in Worksheets
wS.cells.sort Key1:=wS.Range("L2"), Header:=xlYes
Next
End Sub

--

Regards,
Nigel




"Ren" wrote in message
...
Hi Tom,

i have multiple sheets from A to Z
and i need to sort column L
i used this code

Sub sort()
cells.sort Key1:=range("L2"), Header:=xlYes
End Sub

it's working for the single sheet.
but i want it for all the sheets
how to modify your code.i tried but could't get thru.

thanks in advance
Ren

"Tom Ogilvy" wrote:

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 Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=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

sorting in multiple sheets
 

Hey thanks,

i was adding the workbook before the worksheet.so i was getting errors.
anyway it's working perfectly fine
thanks

Ren

"Nigel" wrote:

If it is all sheets in workbook then use this.....

Sub sort()
Dim wS as Worksheet
For Each wS in Worksheets
wS.cells.sort Key1:=wS.Range("L2"), Header:=xlYes
Next
End Sub

--

Regards,
Nigel




"Ren" wrote in message
...
Hi Tom,

i have multiple sheets from A to Z
and i need to sort column L
i used this code

Sub sort()
cells.sort Key1:=range("L2"), Header:=xlYes
End Sub

it's working for the single sheet.
but i want it for all the sheets
how to modify your code.i tried but could't get thru.

thanks in advance
Ren

"Tom Ogilvy" wrote:

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 Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=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