Command to select variable input range

[Guest]

New user. Need help with macro. I would like a a list box that gives the end user a list of cell range names (ie range1, range2, range3, etc. After selecting the input range from the list box, that range will be copied to a second worksheet (sheet2) to a fixed location (a named range - Target). The source ranges (range1, range2, range3, etc) and the traget range are the same dimensions.

The following works if I manually enter the Source range. I am trying to automate this so end user will not enter invalid range names.

Sub test1()
Sheets("sheet1").Range(InputBox("Enter-Range-Name")).copy _
Sheets("sheet2").Range("Target")
End Sub

 

[Jim Rech]

The easiest way to trap a bad range is with VB error trapping, e.g.:

Sub test1()
On Error GoTo ErrTrap
Sheets("sheet1").Range(InputBox("Enter-Range-Name")).Copy _
Sheets("sheet2").Range("Target")
Exit Sub
ErrTrap:
MsgBox "Bad range entered"
End Sub

But requiring users to memorize range names might not be the slickest
interface. You might explore showing a userform with the range names in a
combobox or listbox as you get more experienced with Excel/VB.