Sort ranges on multiple sheets

batkosta

New Member
Joined
May 27, 2005
Messages
33
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Perhaps something like this.
Code:
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
 
Upvote 0
Hello, Batkosta,
this works for me
Code:
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
 
Upvote 0
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.
 
Upvote 0
In both codes posted you could add a condition to only run the code on specific sheets.

Do these sheets have similar names?
 
Upvote 0
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.
 
Upvote 0
Code:
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

Code:
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
 
Upvote 0
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
Code:
    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
 
Upvote 0

Forum statistics

Threads
1,216,516
Messages
6,131,114
Members
449,622
Latest member
lejohnson93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top