Working with Sheet references

G

Guest

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
 
D

Dave Peterson

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.)
 
G

Guest

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
 
D

Dave Peterson

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.
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 said:
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.)
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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

do you mean?

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

--
Regards,
Tom Ogilvy


Dave Peterson said:
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.
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Ress(T) assured that I did.

Thanks for the correction.



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

do you mean?

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

--
Regards,
Tom Ogilvy

Dave Peterson said:
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.
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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PRoblem with Ranges and Sumproduct under VBA 2
working with 2 or vba's on the same sheet 11
Problem with Range reference - Odd error 3
Loop Repeats - PLS HELP 2
Working in Excel 2003 but not Excel 2007. Can not protect sheet. 2
Sorting problem : "The sort reference is not valid..........." 3
Importing a multi-sheet Excel spreadsheet into Access 9
Help with small fix needed 5

Top