This week I wanted to share with you some code that saved me a tonne of time, when I had 600+ worksheets all were numbered 1 to 660 etc, but were auto generated so did not come out in numerical order. I needed to sort them quickly and did not want that manual task on my hands.
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.
What Does The Macro Do?
This Macro will iterate through each of the sheets in your Excel workbook and sort them numerically. It actually compares the current worksheet number to the previous worksheet, if the previous is greater numerically to the current then it moves the sheet in front of it. Excel works its way through all of the worksheets until completed and sorted. Phew no more manually sorting those worksheets.
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 Val to get both sheet names to ensure that all sheet names are sorted numerically.
Step 5. Only if the Previous Sheet name is greater than the Current Sheet name then 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.
That is it. Easy, and tonne of time saved as you can imagine with 600+ worksheets to sort.
Want The Code?
Dim CurrentSheetIndex As Integer
Dim PreviousSheetindex As Integer
For CurrentSheetIndex = 1 To Sheets.Count
For PreviousSheetindex = 1 To CurrentSheetIndex – 1
If Val(Sheets(PreviousSheetindex).Name) >Val(Sheets(CurrentSheetIndex).Name)Then Sheets(CurrentSheetIndex).Move before:=Sheets(PreviousSheetindex)
If you want see see all of the blog posts in the #macromondays series then you can click on this page right here.
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.