Thursday, 22 June 2017

How to sort excel sheets within a workbook

Microsoft Excel is one of the most used spreadsheet application and has lots of features that makes data processing easier. In excel, we can use macros too and in this tutorial, I'll write about sorting excel sheets within a workbook. Follow the steps given below:
1. Open Excel and create some sheets in the workbook. Rename them and arrange them randomly (I'm assuming an example sheet to start with). I've named the sheets as navy call list.
create sheets

2. Now right click on any sheets and click "View Code" as shown in the screenshot below.

view code

3. Now "Microsoft Visual Basic for Applications" window will be opened. Click on "Insert" and click on "Module".

insert module

4. A new module will be created and a blank editor will appear. Copy and paste the following code in the editor:

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
' Prompt the user as which direction they wish to
' sort the worksheets.
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
' If the answer is Yes, then sort in ascending order.
         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
' If the answer is No, then sort in descending order.
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub


4. Now click on "File" and "Close and return to Microsoft excel".

exit from the code editor

5. Now in Excel, click on "View > Macros > View Macros". It will open the saved macros.

view macros

6. In the Macro window, you should see "Sort_Active_Book" macro. Click on it and click "Run". It will open a prompt, click on Yes if you want to sort the sheet in ascending order, click NO if you want to sort the sheet in descending order.

run macro

7. That's it, you can see that the sheets in your workbook are sorted.

sorted sheets

Keep visiting for more tips and tricks.

0 blogger:

Post a Comment

Custom Search Box

Subscribe Our Newsletter

Advertise With Us