Sorting worksheets

[Onesock]

Hi Everyone

Could someone please tell me how to sort worksheets in a workbook alphabetically.

Thanks

[Sadath31]

this code will sort worksheet A-Z

[JBeaucaire]

This should do it:

Option Explicit

Sub SortSheets()
Dim Arr As Variant, vTemp As Variant, ws As Worksheet
Dim i As Long, j As Long, buf As String

'Collect the sheet names into an array
    For Each ws In Worksheets
        buf = buf & "," & ws.Name
    Next ws

    Arr = Split(Mid(buf, 2, Len(buf)), ",")

'from John Walkenbach’s Excel Power Programming with VBA to sort the array
    For i = LBound(Arr, 1) To UBound(Arr, 1) - 1
        For j = i + 1 To UBound(Arr, 1)
            If Arr(i) > Arr(j) Then
                vTemp = Arr(i)
                Arr(i) = Arr(j)
                Arr(j) = vTemp
            End If
        Next j
    Next i

'Sort the worksheets
    For i = LBound(Arr, 1) To UBound(Arr, 1)
        Sheets(Arr(i)).Move after:=Sheets(Sheets.Count)
    Next i
    
End Sub

[Onesock]

Thanks for the responses.
I must say that I dont understand what you have replied. Please , if you dont mind explain to me what/how do I use the code.
Regards
Onesock

[JBeaucaire]

My apologies, this isn't the Programming Forum, so your VBA-only question slipped past me that you might not know how to use macros.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[Onesock]

Thanks again

This works great.
Am I supposed to sign the macros. Im not very familiar with macros at all , but when I open my spreadsheet , a window pops up talks about security levels etc. Not really sure what this means , but it comes up everytime I open the spreadsheet.
Please advise.

[JBeaucaire]

That is normal. The last thing you want to do is turn that warning off. You WANT to know anytime you're opening a workbook that has macros in it and you WANT the opportunity to specifically indicate to Excel to NOT allow the macros to work if you're not sure. It's a mild price to pay to keep your system a little safer from workbooks with unexpected code in them.

If that takes care of your original question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[Onesock]

Thanks for the advice . Really appreciate it .