sorting tabs

excelnewbie · Apr 10th 2003

Can I sort my worksheets (tabs) into alphabetical order?:beergrin:

Dave Hawley · Apr 11th 2003

Hi

Here is a macro that does this. Just add a sheet to your Workbook (or Personal Macro Workbook) named SheetNames. Then hide it.

Code

Sub SortSheets()
Dim i As Integer, ii As Integer
Dim ShtName As String
If ActiveWorkbook.Sheets.Count &gt; 1 Then
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("SheetNames")
       .Columns(20).Clear
       
  For i = 1 To ActiveWorkbook.Sheets.Count
      .Cells(i, 20) = Sheets(i).Name
  Next
  
    .Columns(20).Sort Key1:=.Cells(1, 20), Order1:=xlAscending
    
  For ii = i - 1 To 1 Step -1
   ShtName = .Cells(ii, 20)
    ActiveWorkbook.Sheets(ShtName).Move Before:=ActiveWorkbook.Sheets(1)
  Next
  
End With
Application.ScreenUpdating = True
End If
End Sub

Display More

excelnewbie · Apr 11th 2003

yikes. Thanks very much. Can I just copy this code into the VBA screen? I need a little more help with this as I usually just record macros, not write the code on my own. I don't know enough about it. Can you give me a few more step by steps on how to get this code into my workbook and how to initiate the macro please. I am really really green ! Thanks so much.

:blah:

Dave Hawley · Apr 11th 2003

Hi excelnewbie

No problems. Push Alt+F11 then go to Insert>Module and paste the code straight in. Use this new code as it means you do not need to create a sheet named "SheetNames", the code will do it for you.

Code

Sub SortSheets()
Dim i As Integer, ii As Integer
Dim ShtName As String
If ActiveWorkbook.Sheets.Count &gt; 1 Then
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("SheetNames").Visible = True
Sheets("SheetNames").Delete
On Error GoTo 0
Sheets.Add().Name = "SheetNames"
Application.DisplayAlerts = True
With ThisWorkbook.Sheets("SheetNames")
       .Visible = xlSheetVeryHidden
       .Columns(20).Clear
        
  For i = 1 To ActiveWorkbook.Sheets.Count
     If Sheets(i).Name <> "SheetNames" Then
      .Cells(i, 20) = Sheets(i).Name
     End If
  Next
   
    .Columns(20).Sort Key1:=.Cells(1, 20), Order1:=xlAscending
     
  For ii = i - 2 To 1 Step -1
   ShtName = .Cells(ii, 20)
    ActiveWorkbook.Sheets(ShtName).Move Before:=ActiveWorkbook.Sheets(1)
  Next
   
End With
Application.ScreenUpdating = True
End If
End Sub

Display More

Now simply push Alt+Q and then Alt+F8 Select "SortSheets" and click Options and assign a shortcut key. Click Ok twice and save. Now run the macro via the shortcut key.

sorting tabs

Participate now!

Share