How to select a range whose name is entered in a cell.

[JD Ami]

I have four ranges named A, B, C, D.
I need to be able to copy (and paste) one of them when its name is
entered in a cell.
What is the macro syntax to get the value in a cell and use it to select
a range, please?
I would attach this macro to a button (which I know how to do) to allow
the user to enter the range name and click the button to copy it to a
fixed location (that I know how to specify)
Thanks!.

 

[Bernard Liengme]

This will give you a start
Sub try()
test =Ucase( Range("E1"))
Select Case test
Case "A"
Range("A").Select
Case "B"
Range("B").Select
Case "C"
Range("C_").Select
End Select
Selection.Copy
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E1").Select
End Sub


Note you cannot have a range called C it must be C_ (likewise R_)
The entry in E1 must be committed (with ENTER key or green checkmark in
Formula Bar) before running macro

 

[JD Ami]

Thanks, Bernard!

Works if all ranges below are on same sheet, but in my app A, B,....D are all
in a table on another sheet.

Also, prior to asking for help, I tried another approach... but could not get
the syntax required to make it work.

' Dim cycle As String
' cycle = Evaluate(Range("c_cycle")).Value
' Range("cycle").Select
' Selection.Copy
' Range("c_input_top").Select
' ActiveSheet.Paste
' Application.CutCopyMode = False

 

[JD Ami]

Please disregard... I was able to work around this by starting at the input
sheet, then selecting the sheet where A, B...D are located, continuing your code,
then jumping back to the input sheet.

Thanks, again, Bernard!

cycle = UCase(Range("c_cycle"))
Sheets("tables").Select
Select Case cycle
Case "A"
Range("A").Select
Case "B"
Range("B").Select
Case "C"
Range("C_").Select
Case "D"
Range("D").Select
End Select
Selection.Copy
Sheets("input").Select
Range("c_input_top").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("c_cycle").Select
End Sub