Refernce sheet names in if statement

[Sabosis]

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

 

[Barb Reinhardt]

Sub test()
Dim myWS As Worksheet

'Needed to sest myWS to select
'I think I'd use something besides "Sheet" to access the worksheet.

'Set myWS = ActiveWorkbook.Worksheets(1)
myWS.Select
If myWS.Name = "Jan 08" Then
myWS.Range("A4:Z74").Sort _
Key1:=Range("Z4"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Range("A1:B1").Select

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

 

[dbKemp]

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

dim shCurrent as Excel.Worksheet

For each shCurrent in Thisworkbook.Worksheets' or ActiveWorkbook
If shCurrent.Name = "Jan 08" then
'Do your sort.

Endif
.....

next

 

[Jim Cone]

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



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

 

[Jim Cone]

This revision adjusts the sort range...
'--
Sub PlayItAgainSam_R1()
Dim sht As Object
Dim rCell As Range

'Select sort sheets before running code.
For Each sht In ActiveWindow.SelectedSheets
sht.Select
On Error Resume Next
Set rCell = Switch(sht.Name = "Mar 08", sht.Range("Z4"), _
sht.Name = "Feb 08", sht.Range("Y4"), _
sht.Name = "Jan 08", sht.Range("X4"))
On Error GoTo 0
If Not rCell Is Nothing Then
sht.Range(sht.Range("A4"), rCell.Offset(70, 0)).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