Are you looking for a shortcut to quickly rearrange your worksheets in alphabetical order? Instead of dragging and manually arranging your worksheets, you can use VB for Microsoft® Excel® to quickly rearrange them into alphabetical order.
Excel currently has no built-in function to rearrange your worksheets. Therefore, if you have a workbook with many worksheets, the task of rearranging them may become long and drawn out, but luckily there is a way for this to be simplified. Follow our step by step instructions on how that can be done.
You are welcome to download the workbook to practice.
Applies to: Microsoft® Excel® 2013 and 2016.
- Press ALT + F11 to open VB for Excel.
- From the Menu bar, Select Insert–Module.
- On the right hand side, copy & paste the Visual Basic coding below:
‘ sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
Application.ScreenUpdating = False
sCount = Worksheets.Count
If sCount = 1 Then Exit Sub
For i = 1 To sCount – 1
For j = i + 1 To sCount
If Worksheets(j).Name < Worksheets(i).Name Then
- From the Menu bar, select File, Close and Return to MS Excel.
- Press ALT + F8 to get a list of macros.
- Select Sortsheets.
- Select Run.
- When saving the workbook, Save As Macro Enabled Workbook.
- Ensure that you set the appropriate macro security level:
- Select the File Tab
- Select the Excel Options button
- On the left side of the dialogue box, select Trust Centre
- On the right side, select the Trust Center Settings button
- On the left side, select Macro settings
- Select Disable all macros with notification
- Select OK
The worksheets will quickly be sorted in ascending order, thus saving you time.
Enjoyed this Excel Tip?
Join over 13,000+ subscribers in receiving our weekly tips and tricks in your inbox and to get access to all our Excel on Steroids Tips and Tricks e-books.