Hello Excellers, I am happy to bring you another Excel Macro Tip today in the #macromonday blog post series. This macro is 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 salespeople. It is really handy to be able to sort these worksheets by name with a click rather than rearrange them if a salesperson leaves or a new one joins the company. Or if you have spreadsheet users that prefer everything sorted, all of the time. So, let’ get on with another Excel tip.
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?
Preparation Before Writing The VBA Code
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either save this macro in your Personal Macro Workbook, or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. It makes sense to store this particular Excel macro in the Personal Workbook as it could be useful in multiple Excel workbooks I want to sort alphabetically.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro
We need start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply Sort_Worksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]
Sub Sort_Worksheets()
End Sub
[/stextbox]
Declaring Variables
The next step in the code is to declare a couple of variables. This ensures that Excel creates a memory container for each of them.
[stextbox id=’info’]
Dim CurrentSheetIndex As Integer ‘this holds the current sheet iteration’
Dim PreviousSheetindex As Integer ‘this holds the previous sheet iteration’
[/stextbox]
Beginning The Iteration Through The Worksheets.
Excel starts iteration counting for both variables. The PreviousSheetIndex is set to 1 behind the CurrentSheetIndex.
[stextbox id=’info’]
For CurrentSheetIndex = 1 To Sheets.Count
For PreviousSheetindex = 1 To CurrentSheetIndex – 1
[/stextbox]
Setting The Sheet Names To Uppercase, the Sorting The Sheets
Excel uses the UCase to get both sheet names in upper case to ensure that all sheets names sorted evenly. 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.
[stextbox id=’info’]
If UCase(Sheets(PreviousSheetindex).Name) >UCase(Sheets(CurrentSheetIndex).Name) Then
Sheets(CurrentSheetIndex).Move before:=Sheets(PreviousSheetindex)
End If
[/stextbox]
Looping Through All And Increasing The Increments.
The looping begins again. Every iteration 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.
[stextbox id=’info’]
Next PreviousSheetindex
Next CurrentSheetIndex
End Sub
[/stextbox]
Want The Code?
[stextbox id=’info’]
Sub Sort_Worksheets()
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)
End If
Next PreviousSheetindex
Next CurrentSheetIndex
End Sub
[/stextbox]
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 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 Monday series. Click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
