Working with Sheet references
 

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

Working with Sheet references
 

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

Working with Sheet references
 

Dave thanks so much for the help on this. I have another question which is,
how do I construct a formula with this new information, ie pathname.

I have tried the following to no avail;
xlApp.Range("g27").Formula = "=[""Release Plan
(1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)"

and
"='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month"

And
"=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)"

But I seem to have the syntax all wrong for some reason.

HELP!
Thanks
Jeff

"Dave Peterson" wrote:

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

Working with Sheet references
 

I'd let excel figure things out:

dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range(Plan_month)

'Plan_month is a variable or a real range name?
'if it's a range name (insert|name), then
dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range("Plan_month")

Then later...

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true)


======
I think I'd be more specific than xlApp.range("g27"), too.

Jeff wrote:

Dave thanks so much for the help on this. I have another question which is,
how do I construct a formula with this new information, ie pathname.

I have tried the following to no avail;
xlApp.Range("g27").Formula = "=[""Release Plan
(1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)"

and
"='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month"

And
"=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)"

But I seem to have the syntax all wrong for some reason.

HELP!
Thanks
Jeff

"Dave Peterson" wrote:

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


--

Dave Peterson

Working with Sheet references
 

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true)

do you mean?

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.address(external:=true)

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
I'd let excel figure things out:

dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range(Plan_month)

'Plan_month is a variable or a real range name?
'if it's a range name (insert|name), then
dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range("Plan_month")

Then later...

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true)


======
I think I'd be more specific than xlApp.range("g27"), too.

Jeff wrote:

Dave thanks so much for the help on this. I have another question which
is,
how do I construct a formula with this new information, ie pathname.

I have tried the following to no avail;
xlApp.Range("g27").Formula = "=[""Release Plan
(1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)"

and
"='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month"

And
"=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)"

But I seem to have the syntax all wrong for some reason.

HELP!
Thanks
Jeff

"Dave Peterson" wrote:

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


--

Dave Peterson

Working with Sheet references
 

Ress(T) assured that I did.

Thanks for the correction.



Tom Ogilvy wrote:

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true)

do you mean?

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.address(external:=true)

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I'd let excel figure things out:

dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range(Plan_month)

'Plan_month is a variable or a real range name?
'if it's a range name (insert|name), then
dim myRng as range
set myrng = Workbooks("Release Plan (1,2,3,4).xls") _
.Worksheets(ShtRef).Range("Plan_month")

Then later...

workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true)


======
I think I'd be more specific than xlApp.range("g27"), too.

Jeff wrote:

Dave thanks so much for the help on this. I have another question which
is,
how do I construct a formula with this new information, ie pathname.

I have tried the following to no avail;
xlApp.Range("g27").Formula = "=[""Release Plan
(1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)"

and
"='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month"

And
"=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)"

But I seem to have the syntax all wrong for some reason.

HELP!
Thanks
Jeff

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson