Define range selection from cell content

Question

I want to define and copy a range of cells based on the value contained within a cell.

If cell W2 contains the entry "B6:B12", then the code will select that range and copy the contents into Column X

Sub RangeSel()
Dim rng As Range
Dim Sel As String

Sel = Range("W2").Value
Set rng = Range("Sel").Copy(Range(Range("X2"), Range("X2").End(xlDown)))


End Sub

Answer 1

Your description is a bit enigmatic. Something like that?

Sub RangeSel()
Dim rng As Range
Dim Sel As String

    Sel = Range("W2").Value
    Set rng = Range(Sel)
    rng.Copy
    Range(Range("X2"), Range("X2").End(xlDown)).PasteSpecial xlPasteAll

End Sub

Answer 2

Is this what you are looking for?

Sub RangeSel()
    Dim rng As Range
    Dim Sel As String

    Sel = Range("W2").Value
    Set rng = Range(Sel)

    rng.Copy

    Range("X2").PasteSpecial

End Sub

You tried to set a range and copy at the same time, does not work

Answer 3

If you want to Copy >> Paste (not using PasteSpecial) then you can do it with 1 line of code, see below :

Sub RangeSel()

Dim rng As Range
Dim Sel As String

Sel = Range("W2").Value
Set rng = Range(Sel)

' optional : make sure there is a valid range
If Not rng Is Nothing Then
    rng.Copy Destination:=Range("X2")  ' copy>>paste in 1 line, paste at column X second row
End If

End Sub