Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. -- needVBAhelp |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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 "needVBAhelp" wrote: 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. -- needVBAhelp |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. -- needVBAhelp "Joel" wrote: 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 "needVBAhelp" wrote: 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. -- needVBAhelp |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. "needVBAhelp" wrote: 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. -- needVBAhelp "Joel" wrote: 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 "needVBAhelp" wrote: 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. -- needVBAhelp |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. -- needVBAhelp "Joel" wrote: 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. "needVBAhelp" wrote: 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. -- needVBAhelp "Joel" wrote: 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 "needVBAhelp" wrote: 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. -- needVBAhelp |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. "needVBAhelp" wrote: 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. -- needVBAhelp |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. -- needVBAhelp "JLGWhiz" wrote: 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. "needVBAhelp" wrote: 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. -- needVBAhelp |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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 needVBAhelp wrote: 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. -- needVBAhelp -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. "needVBAhelp" wrote: 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. -- needVBAhelp "JLGWhiz" wrote: 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. "needVBAhelp" wrote: 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. -- needVBAhelp |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
Don & Dave,
Each of your suggestions worked great. Thanks much. Thanks to Joel as well for staying with me through this. -- needVBAhelp "Dave Peterson" wrote: 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 needVBAhelp wrote: 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. -- needVBAhelp -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
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. needVBAhelp wrote: Don & Dave, Each of your suggestions worked great. Thanks much. Thanks to Joel as well for staying with me through this. -- needVBAhelp "Dave Peterson" wrote: 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 needVBAhelp wrote: 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. -- needVBAhelp -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goto Worksheet with Defined Range Name
Dave,
You are correct. I did set rngtocopy as a range name within the VBA code. -- needVBAhelp "Dave Peterson" wrote: 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. needVBAhelp wrote: Don & Dave, Each of your suggestions worked great. Thanks much. Thanks to Joel as well for staying with me through this. -- needVBAhelp "Dave Peterson" wrote: 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 needVBAhelp wrote: 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. -- needVBAhelp -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application-defined or object-defined error (worksheet, range, sel | Excel Programming | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Find defined Name in List, then Goto | Excel Programming | |||
Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
Range - Application Defined or Object Defined Error | Excel Programming |