Retrieving range string from named range
 

I have an input box that defines a range, which is then named, i.e. myRange.

Later in the program, I have need of the String version of the range that
the user entered.

To be specific, the following line of code DOES work:

Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value

However, the same line, using the SAME range, just in a named format, does
NOT work:

Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value

Does this make sense?

Retrieving range string from named range
 

If myrange is truly a range found in the listing in Insert=Names=Define

then
Dim rng as Range
set rng = Sheets(1).Range("MyRange")
Sheets(i).Range(rng.Address).Value = rng.Value

or

With Sheets(1).Range("MyRange")
Sheets(i).Range(.Address).Value = .Value
End With

--
Regards,
Tom Ogilvy


"clapper" wrote in message
...
I have an input box that defines a range, which is then named, i.e.
myRange.

Later in the program, I have need of the String version of the range that
the user entered.

To be specific, the following line of code DOES work:

Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value

However, the same line, using the SAME range, just in a named format, does
NOT work:

Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value

Does this make sense?

Retrieving range string from named range
 

Tom,

No, the range is not found in 'Names'-'Define'. The sheet is protected, so
all of that is greyed out.

Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is
being created in the macro with the following code:

Dim ItemRange As Range
Set ItemRange = Application.InputBox(prompt:="Select Range of Items...",
Type:=8)

Does that shed any light on the issue?

"Tom Ogilvy" wrote:

If myrange is truly a range found in the listing in Insert=Names=Define

then
Dim rng as Range
set rng = Sheets(1).Range("MyRange")
Sheets(i).Range(rng.Address).Value = rng.Value

or

With Sheets(1).Range("MyRange")
Sheets(i).Range(.Address).Value = .Value
End With

--
Regards,
Tom Ogilvy


"clapper" wrote in message
...
I have an input box that defines a range, which is then named, i.e.
myRange.

Later in the program, I have need of the String version of the range that
the user entered.

To be specific, the following line of code DOES work:

Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value

However, the same line, using the SAME range, just in a named format, does
NOT work:

Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value

Does this make sense?

Retrieving range string from named range
 

Yes, you have a variable of type Range which is referencing a specific set
of cells in a specific worksheet. So to use that:

Sheets(i).Range(ItemRange.Address).Value = ItemRange.Value

--
Regards,
Tom Ogilvy

"clapper" wrote in message
...
Tom,

No, the range is not found in 'Names'-'Define'. The sheet is protected,
so
all of that is greyed out.

Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is
being created in the macro with the following code:

Dim ItemRange As Range
Set ItemRange = Application.InputBox(prompt:="Select Range of Items...",
Type:=8)

Does that shed any light on the issue?

"Tom Ogilvy" wrote:

If myrange is truly a range found in the listing in
Insert=Names=Define

then
Dim rng as Range
set rng = Sheets(1).Range("MyRange")
Sheets(i).Range(rng.Address).Value = rng.Value

or

With Sheets(1).Range("MyRange")
Sheets(i).Range(.Address).Value = .Value
End With

--
Regards,
Tom Ogilvy


"clapper" wrote in message
...
I have an input box that defines a range, which is then named, i.e.
myRange.

Later in the program, I have need of the String version of the range
that
the user entered.

To be specific, the following line of code DOES work:

Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value

However, the same line, using the SAME range, just in a named format,
does
NOT work:

Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value

Does this make sense?

Retrieving range string from named range
 

That did it. Thanks!

"Tom Ogilvy" wrote:

Yes, you have a variable of type Range which is referencing a specific set
of cells in a specific worksheet. So to use that:

Sheets(i).Range(ItemRange.Address).Value = ItemRange.Value

--
Regards,
Tom Ogilvy

"clapper" wrote in message
...
Tom,

No, the range is not found in 'Names'-'Define'. The sheet is protected,
so
all of that is greyed out.

Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is
being created in the macro with the following code:

Dim ItemRange As Range
Set ItemRange = Application.InputBox(prompt:="Select Range of Items...",
Type:=8)

Does that shed any light on the issue?

"Tom Ogilvy" wrote:

If myrange is truly a range found in the listing in
Insert=Names=Define

then
Dim rng as Range
set rng = Sheets(1).Range("MyRange")
Sheets(i).Range(rng.Address).Value = rng.Value

or

With Sheets(1).Range("MyRange")
Sheets(i).Range(.Address).Value = .Value
End With

--
Regards,
Tom Ogilvy


"clapper" wrote in message
...
I have an input box that defines a range, which is then named, i.e.
myRange.

Later in the program, I have need of the String version of the range
that
the user entered.

To be specific, the following line of code DOES work:

Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value

However, the same line, using the SAME range, just in a named format,
does
NOT work:

Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value

Does this make sense?