Adjust recorded macro to run for 50 sheets

[ste33uka]

Hi could any adjust this recorded macro so it would run for 50 sheets?
Recorded macro is used to sort data via column
Sheets are named 1,2,3,4,5,6,7 and so on
Or maybe somone has a better way to do this ?
Thanks

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B78").Select
    ActiveWindow.SmallScroll Down:=63
    Range("B78:D157").Select
    ActiveWorkbook.Worksheets("1 ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("1 ").Sort.SortFields.Add2 Key:=Range("D78:D157" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("1915 ").Sort
        .SetRange Range("B78:D157")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=-54
    Range("E78").Select
    ActiveWindow.SmallScroll Down:=54
    Range("E78:G157").Select
    ActiveWorkbook.Worksheets("1 ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("1 ").Sort.SortFields.Add2 Key:=Range("G78:G157" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("1915 ").Sort
        .SetRange Range("E78:G157")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Michael M]

UNTESTED

Sub Macro1()
Dim n As Integer
 For n = 1 To 50
    Worksheets(n).Activate
    Range("B78:D157").Select
    ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("D78:D157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B78:D157")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E78:G157").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("G78:G157" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("E78:G157")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next n
End Sub

 

[ste33uka]

UNTESTED

Sub Macro1()
Dim n As Integer
For n = 1 To 50
    Worksheets(n).Activate
    Range("B78:D157").Select
    ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("D78:D157"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B78:D157")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E78:G157").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("G78:G157" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("E78:G157")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next n
End Sub

Thanks alot, that works great

 

[Michael M]

Glad to help...