Hello Excellers, I am happy to bring you another Excel Macro Tip today. One that I use a lot at work – Sorting my Excel sheets alphabetically by name. I generate a lot of reports, and those reports sometimes contain a lot of worksheets to represent sales people. It is really handy to be able to sort these worksheets by name with a click rather that rearrange them if a sales person leaves or a new one joins the company.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
Download Macro Mondays Example Files.
What Does The Macro Do?
This Macro will iterate through each of the sheets in your Excel workbook and sort them alphabetically. It actually compares the current worksheet name to the previous worksheet, if the previous is greater alphabetically to the current then it moves the sheet in front of it. Excel works its way through all of the worksheet names until completed and sorted. Easy huh?. Much easier than manually moving worksheets around your Excel workbook.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xbl in the Project Window. I have stored this macro in my Personal Macro Workbook, so it is available to use whenever I start Excel and can apply it to any Excel workbook I want to.
Step 2. We need to declare a couple of variables. This ensures that Excel creates a memory container for each of them.
Dim CurrentSheetIndex As Integer (this holds the current sheet iteration)
Dim PreviousSheetindex As Integer (this holds the previous sheet iteration)
Step 3. Excel starts iteration counting for both variables. The PreviousSheetIndex is set to 1 behind the CurrentSheetIndex.
Step 4. This step is where Excel uses the UCase to get both sheet names in upper case to ensure that all sheets names sorted evenly.
Step 5. Only if the Previous Sheet name is greater than the Current Sheet name will the Current Sheet be moved to a position before the Previous Sheet using the Move method.
Step 6. The Loop is started again, and every iterations increases the increments of both variables by 1 number until the last worksheet is reached. The macro ends when all iterations have been carried out.
Want The Code?
Dim CurrentSheetIndex As Integer
Dim PreviousSheetindex As Integer
For CurrentSheetIndex = 1 To Sheets.Count
For PreviousSheetindex = 1 To CurrentSheetIndex – 1
If UCase(Sheets(PreviousSheetindex).Name) >UCase(Sheets(CurrentSheetIndex).Name)Then Sheets(CurrentSheetIndex).Move before:=Sheets(PreviousSheetindex)
Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.