Refernce sheet names in if statement
 

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

Refernce sheet names in if statement
 

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
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Sabosis" wrote:

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

Refernce sheet names in if statement
 

On Oct 31, 1:28 pm, Sabosis wrote:
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

Refernce sheet names in if statement
 

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

Refernce sheet names in if statement
 

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
--
Jim Cone
Portland, Oregon USA