1

I want to sort sheets in my excel file. I found this code which works. But the problem is that my sheet names are 1_abc, 2_adf, 3_dasf, 11_ad etc. This code puts 11_ad before 2_adf and 3_dasf. I would like to sort sheets based upon number before "_" (first underscore) in my sheet name. How could I do the same?

################################UPDATE1

I modified the code as below. But it is sorting in descending order :(. I want to sort in ascending order

Option Explicit

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 CLng(Split(Worksheets(N).Name, "_")(0)) > _
   CLng(Split(Worksheets(M).Name, "_")(0)) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If CLng(Split(Worksheets(N).Name, "_")(0)) > _
   CLng(Split(Worksheets(M).Name, "_")(0)) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            End If
        Next N
    Next M

End Sub
3
  • Relabel your worksheets 01_abc, 02_adf, 03_dasf, 11_ad etc. Commented Apr 12, 2017 at 22:33
  • You're using the > comparison in both blocks - look again at the original code Commented Apr 12, 2017 at 22:39
  • thanks Tim. It is working now Commented Apr 12, 2017 at 22:41

1 Answer 1

6

Instead of this:

If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then     

you need something like this:

If CLng(Split(Worksheets(N).Name,"_")(0)) > _
   CLng(Split(Worksheets(M).Name,"_")(0)) Then
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks. I tried but it is giving problem in update1 above
You need one block with > and one with <
i have sheets like 01_Sheet2, 02_Sheet3, 1_Sheet1 and this vba code is putting 1_Sheet1 at the end. I would 1_Sheet1 to apper before 02_Sheet3 as '1' is < '02'. How should i modify this code?
That's quite different from your original question - You should post a new question for this

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.