Sort Sheets

[ayazgreat]

Hi

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

Thanks in advance

 

[VoG]

Here's an example http://www.vbaexpress.com/kb/getarticle.php?kb_id=72

 

[Stormseed]

Look here:

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

 

[ayazgreat]

Thanks both of you

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

 

[ayazgreat]

Thanks both of you

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

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

 

[ayazgreat]

ok vog I resolved it

LastWSToSort = Worksheets.Count - 1

 

[Stormseed]

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]

Try (untested)

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]

Thank you very much both of you

VOG

I changed it here

LastWSToSort = Worksheets.Count - 1