Sort WorkSheets by Name

Sometime while working on some data in spreadsheets, you might want to sort the sheets by their name.
When you have so many sheets that you do not want to sit and sort it manually, you may use the following code to get it done by VBA for you.
– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Option Explicit
Private i, j, k As Integer
Private N As Integer
Private M As Integer
Private FirstWSToSort, LastWSToSort As Integer
Private SortDescending As Boolean
Private AscDesc

Private Sub SortWorksheets()
'  _________________________________________________________________
'  Code Starts Here
'  Gets the permission from the user to sort the worksheets
'  Written By :   Selva V Pasupathy
'                 HSBC Global Resourcing
'                 Hyderabad
   AscDesc = MsgBox(prompt:="Sort worksheets in ascending order?", _
                             Title:="Sort Order", _
                             Buttons:=vbYesNoCancel)
'  _________________________________________________________________
'  If the user clicks "YES" then it sorts in ascending order or else
'  the sorting is done in descending order
   
   If AscDesc = vbYes Then
      SortDescending = False
   ElseIf AscDesc = vbNo Then
      SortDescending = True
   Else
      Exit Sub
   End If

'  _________________________________________________________________

   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 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
'  _________________________________________________________________
'  Code Ends here
End Sub

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: