Sort ranges on multiple sheets

[batkosta]

Hello Again,

I've got the following macro which sorts people by name on sheet 1.
I've also got 5 identical sheets in the workbook, which represent 5 weeks.
I would like to be able to sort the same range on all 5 sheets simultaneously with the click of one button.

Sub Sort_by_NAME()
Range("A3:R112").Sort Key1:=Range("A3"), Order1:=xlAscending, _
Key2:=Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A3").Select
End Sub

Many thanks,
K.

 

[Norie]

Perhaps something like this.

For Each ws In Worksheets
     ws.Range("A3:R112").Sort Key1:=Range("A3"), Order1:=xlAscending, _ 
Key2:=Range("B3"), Order2:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, _ 
Orientation:=xlTopToBottom 
Next ws

 

[erik.van.geit]

Hello, Batkosta,
this works for me

Sub Sort_by_NAME()
Application.ScreenUpdating = False
For Each sh In Sheets
    With sh
        '.Select
        .Range("A3:R112").Sort Key1:=.Range("A3"), Order1:=xlAscending, _
        Key2:=.Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
        '.Range("A3").Select 'impossible without activating sheet
    End With
Next sh
Application.ScreenUpdating = True
End Sub

kind regards,
Erik

 

[batkosta]

Thank you both, I forgot to mention that I have more sheets in the same workbook which I would like to leave as they are. I guess I have to create an array of the 5 sheets I have to sort but I cannot remember the code..


Thanks again.

 

[Norie]

In both codes posted you could add a condition to only run the code on specific sheets.

Do these sheets have similar names?

 

[batkosta]

They are named: 'Week 1' through to 'Week 5'.
I remember some ago I did a similar code where I created an array of specific sheets and then used the array instead of Worksheets but I cannot remmeber the exact clauses.

 

[Norie]

For Each ws In Worksheets 
     If Left(ws.Name, 4) = "Week" Then
          ws.Range("A3:R112").Sort Key1:=Range("A3"), Order1:=xlAscending, _ 
Key2:=Range("B3"), Order2:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, _ 
Orientation:=xlTopToBottom 
     End If
Next ws

or

For Each ws In Worksheets
     Select Case ws.Name
        Case "Week 1", "Week 2", "Week 3", "Week 4", "Week 5"
             ws.Range("A3:R112").Sort Key1:=Range("A3"), Order1:=xlAscending, _
             Key2:=Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom
        Case Else
            ' do nothing
     End Select
Next ws

 

[batkosta]

This worked great!

Thanks again.

 

[erik.van.geit]

This worked great!

are you sure you ran this on more than one sheet ?

in fact this code triggers an error for me
ERROR: unvalid sortkey (or something like that)

ws.Range("A3:R112").Sort Key1:=Range("A3"), Order1:=xlAscending, _
Key2:=Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

the reason is sorting a worksheet (ws) using a key of the activesheet (Range("A3"))

the right code is
sh.Range("A3:R112").Sort Key1:=sh.Range("A3"), Order1:=xlAscending, _
Key2:=sh.Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
hence my code using "with sh" and dots before the ranges

    With sh
        .Range("A3:R112").Sort Key1:=.Range("A3"), Order1:=xlAscending, _
        Key2:=.Range("B3"), Order2:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom

so to my sense you need to change the code a little

best regards,
Erik