Sort all worksheets : Worksheets « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. Worksheets

Sort all worksheets

 
Sub SortAllSheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cSheets As Integer
    Dim sSheets() As String
    Dim i As Integer
    Set wb = ActiveWorkbook
    
    cSheets = wb.Sheets.Count
    ReDim sSheets(1 To cSheets)
    
    For i = 1 To cSheets
      sSheets(i) = wb.Sheets(i).Name
    Next
    
    Set ws = wb.Worksheets.Add
    For i = 1 To cSheets
      ws.Cells(i, 1).Value = sSheets(i)
    Next
    
    ws.Columns(1).Sort Key1:=ws.Columns(1), _
       Order1:=xlAscending
    
    For i = 1 To cSheets
      sSheets(i) = ws.Cells(i, 1).Value
    Next
    
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
    
    For i = 1 To cSheets
      wb.Sheets(sSheets(i)).Move After:=wb.Sheets(cSheets)
    Next
    
End Sub








Related examples in the same category

1.Do... Loop Until Loops with Worksheets
2.Looping Through Worksheets in a Workbook
3.move the second worksheet to end of workbook
4.performs a simple bubble sort to sort the worksheets in the workbook
5.For...Next Loop with Worksheet
6.The Sheets Collection
7.Grouping Worksheets
8.Dynamic Arrays for worksheet name
9.Use for loop to loop through all worksheets