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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

batkosta

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Do these sheets have similar names?
 

batkosta

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,022
Messages
5,834,994
Members
430,330
Latest member
Syed Yasir Hannan

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
Top