Sorting Worksheets in Ascending Order in Excel – how can I use a macro to do this automatically?
This week’s hint and tip is on sorting worksheets in ascending order in Excel. We are going to go through how you could do this with a macro rather than manually. Macros are covered in our Advanced Excel training course but not this exact example. So we decided to do a hint and tip on it. We are going to go through it now below.
Macros in Excel
Workbooks in Excel can often have many worksheets in them and as the user you will spend time organising them. Most would do this manually by clicking on the worksheet name and dragging it. However there is another way in which you can do this, through the use of macros.
A macro contains VBA code that instructs Excel on how to do something and then carries out the actions for you when you run it. Many people simply record the macro to create the code using View, Macro, Record buttons. In this example however we’ve taken the ‘raw’ approach and coded the program from scratch. The subroutine name is the same as the macro name.
Using Macros to Sort Worksheets into Ascending Order
In this example we are using a macro to sort many worksheets in a workbook into ascending order.
We’ve written and commented such a macro and you can see the code and the workbook example alongside each other below.
The essence of the code is that it has an iterative IF statement that loops through all 5 sheets comparing the previous sheet to the one after and if it is greater in terms of ascending sort sequence move it after it.
Note: Excel sorts numbers before letters
A smart touch at the end of the program is the MsgBox line of code which tells you its worked – always a comfort.
For those familiar with debugging, try stepping through the program using F8 one line of code at a time. This is best achieved by laying out the screens as above so you can see the effect of what each iteration of code does.
The video below talks through the macro and demonstrates it being used in Excel. This is another example of how macros can be used in a more creative way in your spreadsheets.
Take a look below at the video to find out more and then try them out on your own computer!
We hope you have enjoyed this hint and tip on using a macro for sorting worksheets in ascending order. Why not take a look at our previous video hint and tip on how to use the screen recording feature in PowerPoint?