Another way...
'--
Sub PlayItAgainSam()
Dim sht As Object
Dim rCell As Range

'Select sheets before running code.
For Each sht In ActiveWindow.SelectedSheets
sht.Select
On Error Resume Next
Set rCell = Switch(sht.Name = "Jan 08", sht.Range("Z4"), _
sht.Name = "Feb 08", sht.Range("Y4"), _
sht.Name = "Dec 08", sht.Range("X4"))
On Error GoTo 0

If Not rCell Is Nothing Then
sht.Range("A4:Z74").Sort Key1:=rCell, Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
sht.Range("A1:B1").Select
End If
Set rCell = Nothing
Next 'sht
End Sub
--
Jim Cone
Portland, Oregon USA



"Sabosis" wrote in message ...
Hello-

How would you refernce sheet names in a if/then statement? I need to
look at different ranges based on the sheet name, here is my code
below:

If Sheet.Name = "Jan 08" Then
Range("A4:Z74").Select
Selection.Sort Key1:=Range("Z4"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Range("A1:B1").Select
Else
If Sheet.Name = "Feb 08" Then
Range("A4:Y74").Select
Selection.Sort Key1:=Range("Y4"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Range("A1:B1").Select
End If
End Sub