AIM: Sort Sheets in alphabetical order and reverse alphabetical order

NOTE: If you get stuck check the sample code in the downloadable Project workbook. Watch the explainer video to understand how the sorting is done.

---

Why create this Macro?

1. The need: You have a workbook with 25 sheets (both worksheets and charts) but they are not sorted in ascending or descending alphabetical order. You need to quickly sort them before sending them to your colleague for review.
2. Without the Macro: In Excel it would take you a long time because you would need to rearrange each sheet by clicking and dragging it into position.
3. So what?: Imagine sorting a workbook with 25 sheets… Why spend a lot of time getting sheets into order when you know VBA can do it in a blink?
4. The solution: Write a VBA macro that sorts sheets alphabetically (and the reverse) with one click, saving you time and frustration! The sample code is relatively short (only 9 lines of code) but the sorting process might challenge your brain! Just think step by step, try to describe in whole sentences what you are trying to achieve, and make sure you watch the explainer video a couple of times if you are stuck.
   
   

---

Set up workbook, create procedure and declare variables

1. Open a new Excel workbook and access the VBA Editor
   
   
2. Create a new code module called Module1
   
   
3. Create a new procedure called Sub Sort_Sheets_Alphabetically()
   
   
4. Declare new variables inside the procedure: (1) Dim “Counter_1” as Integer, (2) Dim “Counter_2” as Integer
   
   

Loop through every sheet, rearrange in alphabetical

1. Write the first For… Next… loop (see module 5 for refresher) that loops Counter_1 from 2 to the total number of sheets in the workbook (note: this is the “Current” sheet)
   
   
2. Write the second For… Next… loop (see module 5 for refresher) that loops Counter_2 from 1 to Counter_1 – 1 (note: this is the “Previous” sheet)
   
   
3. Compare the name of the current sheet and previous sheet using an IF … THEN… statement (note: watch the explainer video to understand what this means, and you can compare the names using “>” which means greater than)
   
   
4. IF the name of the previous sheet > the name of the current sheet (i.e. they are not in alphabetical order) THEN … move the current sheet before the previous sheet (remember: close the IF THEN statement with End If)
   
   
5. Continue both For … Next … loops (remember: use the keywork Next)
   
   

Save your VBA code and test it

1. Save your workbook with a suitable name (e.g. M6 Project 5)
   
   
2. Compare your VBA code with the sample code in the downloadable Module 6 workbook
   
   
3. Test your code by creating 5 sheets and giving them names (e.g. Apple, Banana, Coconut, Date, Elderberry), rearrange them into the wrong order and run the code to sort them alphabetically
   
   
4. If you code doesn’t work, please study the sample code carefully
   
   
5. You will also find it helpful to step through (F8) the sample code in the module “Project_5_Explained” which is what was used for the explainer video above
   
   

Create second procedure to sort reverse alphabetical

1. Create a new procedure called Sub Sort_Sheets_Alphabetically_Reversed()
   
2. You only need to change one line in the code to reverse the sort order… can you figure out which line without looking at the sample code?

---

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]