I think I'd do something like:

Option Explicit
Sub testme()

Dim ShtRef As String
Dim myAddr As String

myAddr = "f5:Z5"
ShtRef = ""
Select Case LCase(Workbooks("book1.xls") _
.Worksheets("sheet1").Range("b2").Value)
Case Is = "rel 1a": ShtRef = "rel 1a"
Case Is = "cis release 1b.published": ShtRef = "rel 1b"
Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr"
Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated"
End Select

If ShtRef = "" Then
'do nothing
Else
On Error Resume Next
Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range(myAddr)
If Err.Number < 0 Then
MsgBox "Invalid sheet name!"
Err.Clear
End If
On Error GoTo 0
End If

End Sub

========
I think it's a good idea to fully qualify your range--like in this line:
Select case lcase(workbooks("book1.xls") _
.worksheets("sheet1").range("b2").value)

And application.goto works so that you don't have to activate a workbook, select
a sheet and then select the range.

(Watch out for typos. This compiled ok, but I didn't recreate the test
workbooks.)


Jeff wrote:

I am trying to call to a second workbook, to a specific sheet, and a specific
range. The Hook here is that I want to select sheets based upon my if then
statements such that if cell B2 = "Rel 1a", then the code would jump to
Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the
range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of
figure out how to assemble the string. Could someone please point me in th
eright direction please.

Jeff
Dim Max As String
Dim Xlmonth As String
Dim ShtRef As String
Dim xlrange As Excel.Range
Dim Project As Object
Dim Page As Object
Dim Totals As Object

If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a"
If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b"
If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR"
If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef =
"Rel 3 Estimated"

Project = xlApp.Windows("Release Plan (1,2,3,4).xls")
Page = xlApp.Worksheets(ShtRef)
Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26))
xlrange = (Project & Page & Totals)
xlrange.Select

--

Dave Peterson