One way:

Public Sub Sort_CounselorNames(sht As Worksheet, pwd As String)
Application.ScreenUpdating = False
sht.Unprotect Password:=pwd
Dim i As Long
For i = 2 To 27
With Cells(8, i).Resize(30, 1)
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next i
Range("B8").Select
sht.Protect Password:=pwd
Application.ScreenUpdating = True
End Sub





In article ,
"Ruan" wrote:

Hello,

Is there a better way to write the below code? I have 26 Columns of data,
which I want the data within each column to be sorted when the User clicks a
command button.


Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With ActiveSheet
.Range("B8:B37").Sort Key1:=.Range("B8:B37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("C8:C37").Sort Key1:=.Range("C8:C37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("D8:D37").Sort Key1:=.Range("D8:D37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("E8:E37").Sort Key1:=.Range("E8:E37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("F8:F37").Sort Key1:=.Range("F8:F37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End With
Range("B8").Select

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan