Macro to copy a Named Range from one Worksheet to another

Question

I have several Named Ranges that contain constant data in one worksheet. I have a target range where one of the Named Ranges will be copied to, on another worksheet.

The Named Range that will be copied is selected based on user input into other cells. I have managed to create the name of the relevant Named Range in a single cell.

What I can't do (as I'm a VBA Noob who thought he could do all this without using VBA!), is create a Macro that reads the relevant cell, and then copies whatever Name Range it reads, into the target range.

Any assistance most humbly and gratefully accepted.

Answer 1

Let's say, the name of your range is MyRange

So to copy the range you have to do this

Range("MyRange").Copy

Now let's assume that Cell A1 of Sheet1 has the word MyRange. In such a scenario, you can retrieve the value of the cell A1 using Range("A1").Value

So

Range("MyRange").Copy

becomes

Range(Range("A1").Value).Copy

Here is a complete example. I am assuming that you want to copy to say Cell A1 of Sheet2

Sub Sample()
    Dim wsI As Worksheet, wsO As Worksheet

    Set wsI = ThisWorkbook.Sheets("Sheet1")
    Set wsO = ThisWorkbook.Sheets("Sheet2")

    wsI.Range(wsI.Range("A1").Value).Copy wsO.Range("A1")
End Sub

Answer 2

i am not sure if thats what you need, but, if you need just to copy the content of the A1 cell from sheet1 to sheet2 for example, just do this:

Plan2.Cells(1, 1).Value = Plan1.Cells(1, 1).Value

you may wnat to encapsulate the code into a sub as well:

Sub copyvalues()
Plan2.Cells(1, 1).Value = Plan1.Cells(1, 1).Value
End Sub

...and finally, you must to insert a button, using onclick() event to fire the sub(which you must to allocate into a module)

sorry my bad english, hope it helps you.

Answer 3

Public Function copyNamevalues(srcName As Name, destName As Name)
    srcName.RefersToRange.Cells.Copy (destName.RefersToRange.Cells)
End Function