selecting a named range based on a cell value

[WimR]

I am trying to select and copy a named range based on the value of a
particular cell.

For example

Range C1:C4 contain 4 cities in Australia and is named AUS_CITIES
Range D14 contain 4 cities in the USA and is named USA_CITIES

The user enters his country in Cell A1: - lets say he enters
"Australia".

Based in the value of A1 (Australia) the macro should select the
range AUS_CITIES and copy this range to another worksheet.

Can anybody help me writing the VBA code for this ??

(the actual work that I need to do is a but more complex than this,
but the above example simplifies the issue at hand).

Many thanks for your help.

Wim

 

[aidan.heritage]

I am trying to select and copy a named range based on the value of a
particular cell.

For example

Range C1:C4 contain 4 cities in Australia and is named AUS_CITIES
Range D14 contain 4 cities in the USA and is named USA_CITIES

The user enters his country in Cell A1: - lets say he enters
"Australia".

Based in the value of A1 (Australia) the macro should select the
range AUS_CITIES and copy this range to another worksheet.

Can anybody help me writing the VBA code for this ??

(the actual work that I need to do is a but more complex than this,
but the above example simplifies the issue at hand).

Many thanks for your help.

Wim

Data validation in A1 to ensure he ONLY selects valid data (!). Then
use SELECT CASE within the macro (or even vlookup on the worksheet to
translate the selection to the name)

 

[WimR]

Data validation in A1 to ensure he ONLY selects valid data (!). Then
use SELECT CASE within the macro (or even vlookup on the worksheet to
translate the selection to the name)- Hide quoted text -

- Show quoted text -

Thanks for that . I can easily use the vlookup on the worksheet to
return the named range in a cell. But then, what VBA code do I use to
specify the corresponding named range in the macro , select that range
and copy it. ?

Thanks for your help.

Wim

 

[aidan.heritage]

Thanks for that . I can easily use the vlookup on the worksheet to
return the named range in a cell. But then, what VBA code do I use to
specify the corresponding named range in the macro , select that range
and copy it. ?

Thanks for your help.

Wim- Hide quoted text -

- Show quoted text -

OK, say the formula is in A1 (just for ease)

Dim MyStr
MyStr=range("A1").value
range(mystr).select
etc....etc...etc


(you should hopefully see the logic of that as I've shown that "A1" is
recognizable, which is a string, so replacing "a1" with a string
variable works just as well!