How to copy a range on different pages depending on a cell value?

Question

For example:

on worksheet1, A1 cell value has data validation for a list, the values of the list are:

-hello -goodbye

On worksheet2 i have a data range named "hello" and on worksheet3 i have data range named "goodbye"

What i want is that when i choose from the list hello or goodbye the macro copies the range that is named after the value of A1 cell on worksheet1.

Is there a way to do that? i tried making the cell value a string and then use this code: Range("& string &").copy , but the problem is that i dont specify in wich worksheet is the range that i want to copy, so obviously this doesn't work.

help pls

Answer 1

If you are applying the `.RefersToRange method, you don't have to go any further with a named range using a Workbook scope.

Thisworkbook.names("Hello").referstorange.copy destination:=range("L32")

The destination will have to change to suit your own target and "Hello" should be a reference to your data validation cell but you should get the idea.