• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Sort Sheet Tabs Alphabetically – Excel Macro

Sort Sheet Tabs Alphabetically – Excel Macro

In this blog post, we will create a macro to sort sheet tabs alphabetically with the click of a button.
Unfortunately Excel has no feature built-in to sort sheet tabs. But we can create our own, and with VBA set it up however we like.

This macro will be quite simple, but if you are not very familiar with Excel VBA you can sign up for the online Excel VBA course for beginners. This will help to get a better grasp of some of the techniques involved.

Watch the video below to see the macro being written and then tested, or read on to get the code and a written description.

Watch the Video

The Sort Sheets VBA code

Below is the code used in this macro to sort the sheet tabs.

Sub SortSheets()
Dim s As Integer
Dim i As Integer
For s = 1 To ActiveWorkbook.Sheets.Count
For i = 1 To ActiveWorkbook.Sheets.Count - 1
If UCase$(Sheets(i).Name) > UCase$(Sheets(i + 1).Name) Then
Sheets(i).Move after:=Sheets(i + 1)
End If
Next i
Next s
End Sub

How it Works

The code uses two For loops.

The interior loop which begins with For i = 1 loops through each sheet in the workbook testing if the sheet is greater than the sheet to its right, and moving it if so.

The exterior loop ensures that this process is repeated for as many times as there are sheets to be assured they have all been moved if necessary.

The Ucase$ in the If statement ensures the sheet names are in the same case for testing. Otherwise a sheet with an uppercase starting letter would be viewed differently to one with a lowercase starting letter.

The Ucase$ function converts them to uppercase. The $ sign ensures that the values is returned as a string, whilst Ucase alone returns it as a variant. This keeps our macro lean. With a macro of this size, it was not too important. But was nice practice.

Last Words

This is a simple macro to accomplish the task of sorting sheet tabs alphabetically. The code can be altered slightly to achieve other goals such as sorting descending, or ignoring specific sheet names, or having a custom list such as names of months.

This is a nice challenge for those wanting to learn VBA.

Related Posts:

  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value
  • Live currency exchange rate in Excel thumbnail
    LIVE Currency Exchange Rate in Excel
  • Copy a formula down in Excel
    How to Copy a Formula Down in Excel
  • Case sensitive XLOOKUP feature
    Case Sensitive Lookup with XLOOKUP in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·