CODEWITHSUNDEEP
vbaexcel
samred
samred

Reputation: 3

How to make this recorded macro apply for all sheets in workbook

I have this recorded macro which works for one sheet, but I copied and pasted and changed all sheet names and when I ran it, it only worked for the last sheet (wasnt even correct sheet name). How can I sort all the sheets of the workbook (there's like 8).

Sub Sort_Design_NEB()

    Range("A1").Select
 ActiveWorkbook.Worksheets("NEB_D").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("NEB_D").Sort.SortFields.Add Key:= _
    Range("E2:E55"), SortOn:=xlSortOnValues, Order:=xlAscending,      DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("NEB_D").Sort.SortFields.Add Key:= _
    Range("H2:H55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("NEB_D").Sort.SortFields.Add Key:= _
    Range("G2:G55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("NEB_D").Sort
    .SetRange Range("A1:H55")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Upvotes: 0

Views: 330

Answers (1)

Fernando Madriaga
Fernando Madriaga

Reputation: 458

Try with a loop for all sheets of the workbook. Like this:

Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count
         Range("A1").Select
         With ActiveWorkbook.Worksheets(I)
             .Sort.SortFields.Clear
             .Sort.SortFields.Add Key:= _
             .Range("E2:E55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
             xlSortNormal
             .Sort.SortFields.Add Key:= _
             .Range("H2:H55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
             xlSortNormal
         .Sort.SortFields.Add Key:= _
             .Range("G2:G55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
             xlSortNormal
         End With
         With ActiveWorkbook.Worksheets(I).Sort
             .SetRange Range("A1:H55")
             .Header = xlYes
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
         End With
Next I

Update with statement. Thank Thomas Inzina.

Upvotes: 1

Related Questions