Results 1 to 4 of 4

Thread: [VBA] Unable to sort tabs of selected sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2015
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] Unable to sort tabs of selected sheets

    Code:
    Sub SortSelectedWorksheets()
        '---------------------
        MTxt = MsgBox("- Sort Selected Worksheet(s) -" & vbNewLine & "---------------------" & vbNewLine & _
            "Yes : Ascendingly" & vbNewLine & _
            "No  : Descendingly", vbYesNoCancel)
        '---------------------
        SheetCount = ActiveWindow.SelectedSheets.Count
        '---------------------
        With ActiveWindow
            Select Case (MTxt)
                ' ________________ [ sort ascendingly ] ________________
                Case (vbYes):
                    For i = 1 To SheetCount - 1
                        For j = i + 1 To SheetCount
                            If (.SelectedSheets(j).Name < .SelectedSheets(i).Name) Then
                                .SelectedSheets(j).Move before:=.SelectedSheets(i)
                            End If
                        Next
                    Next
                    
                    ' ________________ [ sort descendingly ] ________________
                Case (vbNo):
                    For i = 1 To SheetCount - 1
                        For j = i + 1 To SheetCount
                            If (.SelectedSheets(j).Name > .SelectedSheets(i).Name) Then
                                .SelectedSheets(j).Move before:=.SelectedSheets(i)
                            End If
                        Next
                    Next
            End Select
            '---------------------
        End With
    End Sub
    I got an error for the above in sorting 5 selected worksheets.

    But I am just unsure which part goes wrong.

    Any hint?

    Thanks a lot!
    Last edited by smallxyz; Sep 27th, 2019 at 11:43 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,185
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: [VBA] Unable to sort tabs of selected sheets

    How about
    Code:
        sheetcount = ActiveWindow.SelectedSheets.Count
        ReDim Shts(1 To sheetcount)
        For i = 1 To sheetcount
          Shts(i) = ActiveWindow.SelectedSheets(i).Name
        Next i
       
        '---------------------
            Select Case (MTxt)
                ' ________________ [ sort ascendingly ] ________________
                Case (vbYes):
                    For i = 1 To UBound(Shts) - 1
                        For j = i + 1 To UBound(Shts)
                            If Shts(j) < Shts(i) Then
                                Sheets(Shts(j)).Move before:=Sheets(Shts(i))
                            End If
                        Next
                    Next
                    
                    ' ________________ [ sort descendingly ] ________________
                Case (vbNo):
    As soon as a sheet is moved it becomes the active sheet, at which only have one active sheet, so your code fails
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jul 2015
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Unable to sort tabs of selected sheets

    Thanks Fluff!
    Problem solved.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,185
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: [VBA] Unable to sort tabs of selected sheets

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •