Sort Sheets

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
Hi

How should sheets be sorted in ascending order except last sheet named "Report".

Thanks in advance
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
Thanks both of you

Vog how should I amend given below codes sort all sheets except last sheet name "Report"
 

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151

ADVERTISEMENT

Thanks both of you

Vog how should I amend given below codes sort all sheets except last sheet name "Report"

Code:
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
         
         'Change the 1 to the worksheet you want sorted first
        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
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

Also, I selected or grouped the worksheets which I wanted to sort and I left the worksheet as is which I did not want the macro to sort and I run the macro after this grouping. It only sorted the worksheets I selected and grouped.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try (untested)

Rich (BB code):
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
         
         'Change the 1 to the worksheet you want sorted first
        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 Worksheets(N).Name <> "Report" Then
                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
            End If
        Next N
    Next M
     
End Sub

Edit: must learn to code faster :)
 

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
Thank you very much both of you

VOG

I changed it here

Code:
LastWSToSort = Worksheets.Count - 1
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top