Using variables to select multiple ranges in VBA Excel

Question

I'm trying to use variables for multiple ranges but I haven't figured a way to do this. As you can see in my code, I want to copy multiple rows based on a for, but can't figure out how to include the variable "v". IN other words, how can I do Range("F"& v & ":K" &v, etc....).Select

For v = 7 To lastr
        Sheets(1).Select
        Range("F7:K7,M7:Q7,AE7:AI7,AW7:BA7,BO7:BS7,CG7:CK7,CY7:DJ7").Select
        Selection.Copy

        For w = 2 To destrow

            Sheets("Data").Select
            Range("H" & w).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
            w = w + 42
        Next w
    Next v

Any help is appreciated

Tim Williams · Accepted Answer · 2014-11-12 18:39:34Z

2

Range("F1:K1,M1:Q1,AE1:AI1,AW1:BA1,BO1:BS1,CG1:CK1,CY1:DJ1").offset(v-1,0).Copy

Note there's no need to Select first - you cvan Copy directly

answered Nov 12, 2014 at 18:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cheezsteak Over a year ago

Also Sheets("Data").Range("H" & w).PasteSpecial Paste:=xlPasteValues, Transpose:=True

Collectives™ on Stack Overflow

Using variables to select multiple ranges in VBA Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related