Goto Worksheet with Defined Range Name

[Guest]

I have a macro that defines a range name as follows:

Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

I do not know in advance the worksheet that will ultimately contain the
range for "rngCopyTo".

Later in the macro, I want to goto "rngCopyTo".

I have tried the following, but I get an error message unless the selected
worksheet contains the range for "rngCopyTo". Is there a VBA code that will
find and select the worksheet that contains "rngCopyTo"?

rngCopyTo.Select

Thank you for any help.

 

[Guest]

The VBA help show how to use the GOTO method
This example selects cell A154 on Sheet1 and then scrolls through the
worksheet to display the range.

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"), _
scroll:=True

You can replace the range with a named range
Sub test()
Application.Goto Reference:=Application.Range("rngCopyTo"), _
scroll:=True
End Sub

 

[Guest]

Thank you, Joel. I tried:

Application.Goto Reference:=Application.Range("rngCopyTo"), _

scroll:=True

But I got the following error message:

Method 'Range' of object '_Application' failed

Any suggestions? Thanks again.

 

[Guest]

You will get your error if the name range doesn't exist on you worksheet.
Make sure you have a named range called rngCopyTo. The names range doesn't
needed quotes.

 

[Guest]

Joel,

The problem is when my macro is run, I do not know which worksheet will
contain the range name. I am trying to goto the worksheet that happens to
contain the range name. Is this possible?

Thanks one more time.

 

[Don Guillett]

This will take you to the named range on another sheet. However, it is not
necessary or desirable to select to work with a range.

Sub gotonamedrange()
Application.Goto Range(""rngCopyTo")
End Sub

 

[Guest]

I think you might neet two InputBox variables to accomplish what you want to
do.
Psuedo code:
cpytoWks = InputBox("Enter Destination Worksheet Name". "Worksheet")
rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)
Wks1.Range("A1:C5").Copy Sheets(cpytoWks).Range(rngCopyTo)

That gets you to the sheet and range without a GoTo.

 

[Guest]

Yes, this works. I did not want to have to manually enter the worksheet name,
but that looks like the solution.

Thanks much for your help.

 

[Dave Peterson]

Your RngCopyTo isn't a name of a range (except by coincidence)--it's just an
object/range variable.

Option Explicit
Sub testme01()

Dim RngCopyTo As Range

Set RngCopyTo = Nothing
On Error Resume Next
Set RngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)
On Error GoTo 0

If RngCopyTo Is Nothing Then
'user hit cancel--or did you do that earlier in the code???
Else
Application.Goto RngCopyTo, scroll:=True
End If

End Sub

 

[Guest]

The best way to write VBA code is to use names ranges on worksheets which you
can manual enter under the worksheet menu Insert - Name - Define. As you add
and delete cells on your worksheets the named ranges will change the same way
formula do when the worksheet is modified. Names include both sheet names
and ranges of cells.

The code I gave you uses the named range rngCopyTo. You can change the
range of cells at any time and the VBA code will not have to be modified.

 

[Guest]

Don & Dave,

Each of your suggestions worked great. Thanks much.
Thanks to Joel as well for staying with me through this.

 

[Dave Peterson]

I would have guessed that Don's code would only work if there was a range named
rngtocopy--not a range variable with that name.

Don & Dave,

Each of your suggestions worked great. Thanks much.
Thanks to Joel as well for staying with me through this.

 

[Guest]

Dave,

You are correct. I did set rngtocopy as a range name within the VBA code.