Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using IF to refernce another cell | Excel Programming | |||
Cell names = sheet names | Excel Worksheet Functions | |||
Circular Refernce | Excel Discussion (Misc queries) | |||
Using Sheet names & Workbook names in VBA coding | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming |