CODEWITHSUNDEEP
vbaexcel
chupeman
chupeman

Reputation: 1115

Using variables to select multiple ranges in VBA Excel

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

Upvotes: 0

Views: 2049

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 2

Related Questions