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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
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,061
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,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,676
Messages
5,573,603
Members
412,538
Latest member
mrjack
Top