0

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
Improve this question

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, and apologies for the vague description. This is exactly what I was looking for!
1

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

Improve this answer

Comments

1

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.