Thread: MS Excel – VBA – Sorting worksheet - CODE

Quando refiro-me a pasta, estou citando as inúmeras planilhas dentro de um mesmo arquivo .XLS.

É habitual a utilização de diversas pastas em um mesmo arquivo Excel. Por decorrência, torna-se totalmente necessária que elas estejam organizadas por nome. Geralmente ocorre o contrário: Criamos várias pastas, ordenamos algumas e deixamos outras pelo caminho. No final estamos perdidos com várias pastas perdidas em nossas planilhas.

. . .

Veja a solução em:
http://inanyplace.blogspot.com/2008/...as-pastas.html

A&A - IN ANY PLACE
inanyplace@gmail.com

Here is some code I obtained quite a while ago from this site:

Code:

Sub SortSheets()
' This routine sorts the sheets of the active workbook in ascending order.
    
    Dim SheetNames() As String
    Dim i As Long
    Dim SheetCount As Long
    Dim Item As Object
    Dim OldActive As Object
    
    If ActiveWorkbook Is Nothing Then Exit Sub
    
' Check for protected workbook structure
    If ActiveWorkbook.ProtectStructure Then
        MsgBox ActiveWorkbook.Name & " is protected.", vbCritical, "Cannot Sort Sheets"
        Exit Sub
    End If
    
' Disable CTRL + Break
    Application.EnableCancelKey = xlDisabled
    
' Determine the number of sheets and ReDim the array
    SheetCount = ActiveWorkbook.Sheets.Count
    ReDim SheetNames(1 To SheetCount)
    
    Set OldActive = ActiveSheet

' Fill array with sheet names
    For i = 1 To SheetCount
        SheetNames(i) = ActiveWorkbook.Sheets(i).Name
    Next i
    
' Sort the array in ascending order
    Application.ScreenUpdating = False
    Call BubbleSort(SheetNames)
    
' Move the sheets
    For i = 1 To SheetCount
        ActiveWorkbook.Sheets(SheetNames(i)).Move before:=ActiveWorkbook.Sheets(i)
    Next i
    
    OldActive.Activate
    Application.ScreenUpdating = True
    
End Sub

If you need another language, please post in the non-English Excel forum.

HTH

Jason