CODEWITHSUNDEEP
vbasortingloopsselectworksheet
user3694581
user3694581

Reputation: 11

Sort All Worksheets in Workbook

I am trying to sort all worksheets in my workbook but the following code keeps giving me an error. Any and all help would be most appreciated.

Dim i As Worksheet

For Each i In ThisWorkbook.Sheets
    Worksheets(i).Columns("A").Select
    Selection.Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
Next

End Sub

Upvotes: 1

Views: 305

Answers (2)

Alter
Alter

Reputation: 3464

  1. You are not using i as an index, so "Worksheets(i)" is really "Worksheets(Worksheet)" which would cause an error. Just use "i"
  2. Range("A1") is not linked to a worksheet object, it should be i.Range("A1")

For extra efficiency, don't use select (it's unnecessary to sort). The following code should work:

Sub aaa()
    Dim i As Worksheet
    Dim r As Range

    For Each i In ThisWorkbook.Sheets
        Set r = i.Range("A:A")
        r.Sort key1:=i.Range("A1"), order1:=xlAscending, Header:=xlYes
    Next
End Sub

Upvotes: 1

Othya
Othya

Reputation: 420

A little more than that, but what I've seen on the internet if you want to sort alphabetically, do something like this:

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
    FirstWSToSort = 1
    LastWSToSort = Worksheets.Count
Else
    With ActiveWindow.SelectedSheets
        For N = 2 To .Count
            If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                MsgBox "You cannot sort non-adjacent sheets"
                Exit Sub
            End If
        Next N
        FirstWSToSort = .Item(1).Index
        LastWSToSort = .Item(.Count).Index
     End With
End If

For M = FirstWSToSort To LastWSToSort
    For N = M To LastWSToSort
        If SortDescending = True Then
            If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                Worksheets(N).Move before:=Worksheets(M)
            End If
        Else
            If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
               Worksheets(N).Move before:=Worksheets(M)
            End If
        End If
     Next N
Next M

End Sub

Change SortDescending to true if you want to sort descending.

You can easily change the starting / ending workbook by changing FirstWSToSort and LastWSToSort.

Upvotes: 0

Related Questions