Selecting a named range, the name of the named range is in a cell

[Luc]

What i want to do is the following :

I have a cell which i have named "ExtraRisk".
This cell contains text : example "Electricity", which is also the name of
a range.
Now i want to select this named range "Electricity" & copy the range

How do i do this.

What i have so far is :


Sub Risicos_toevoegen()

.....
Sheets("MASTER").Select

'I'm stuck here....
Application.Goto Reference:="ExtraRisk" 'I do not want to copy the named
range "ExtraRisk", but i want to copy the named range where the value of the
cell "ExtraRisk" refers to (this value is the name of a named range)
.....


Selection.Copy
.....

End Sub

 

[Gary Keramidas]

this may or may not be what you want:

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim rangeToSelect As String
rangeToSelect = ws.Range("extraRisk").Value
ws.Range(rangeToSelect).Select
End Sub

 

[Rick Rothstein]

If I understand you correctly, I think you want this...

Range(Range("ExtraRisk")).Copy

 

[Luc]

Thanxxxxx

Luc

this may or may not be what you want:

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim rangeToSelect As String
rangeToSelect = ws.Range("extraRisk").Value
ws.Range(rangeToSelect).Select
End Sub

--


Gary Keramidas
Excel 2003

 

[michdenis]

Hi,

you mean this : Range(Range("ExtraRisk").address).Copy



| If I understand you correctly, I think you want this...

| Range(Range("ExtraRisk")).Copy

"

 

[Rick Rothstein]

Nope, that is not what I meant. The OP said this...

1) I have a cell which i have named "ExtraRisk".

2) This cell contains text : example "Electricity", which is
also the name of a range.

3) Now i want to select this named range "Electricity" &
copy the range

Numbers 1 and 2 tell me that the contents of Range("ExtraRisk") is the word
Electricity and that Electricity is a named range. Number 3 says the OP
wants to copy Range("Electricity"). Since Range("ExtraRisk") contains the
word "Electricity", we can do a direct substitution, putting
Range("ExtraRisk") in place of the word "Electricity"; hence
Range(Range("ExtraRisk")) points to whatever the named range "Electricity"
points to; so, according to Number 3, we want to copy that...

Range(Range("ExtraRisk")).Copy

I guess the only thing this expression can be faulted for is relying on the
default value of the Range object; perhaps this would have been more to your
liking...

Range(Range("ExtraRisk").Value).Copy

 

[michdenis]

Ok. Thank Rick for your explanation. It works well.



"Rick Rothstein" <[email protected]> a écrit dans le message de groupe
de discussion : (e-mail address removed)...
Nope, that is not what I meant. The OP said this...

1) I have a cell which i have named "ExtraRisk".

2) This cell contains text : example "Electricity", which is
also the name of a range.

3) Now i want to select this named range "Electricity" &
copy the range

Numbers 1 and 2 tell me that the contents of Range("ExtraRisk") is the word
Electricity and that Electricity is a named range. Number 3 says the OP
wants to copy Range("Electricity"). Since Range("ExtraRisk") contains the
word "Electricity", we can do a direct substitution, putting
Range("ExtraRisk") in place of the word "Electricity"; hence
Range(Range("ExtraRisk")) points to whatever the named range "Electricity"
points to; so, according to Number 3, we want to copy that...

Range(Range("ExtraRisk")).Copy

I guess the only thing this expression can be faulted for is relying on the
default value of the Range object; perhaps this would have been more to your
liking...

Range(Range("ExtraRisk").Value).Copy