Moving ranges to copy and paste in Excel VBA

Question

I am attempting to setup a macro to select data from a worksheet(1), paste that data in another worksheet(2), then take the result from the worksheet(2) and paste that information in worksheet(3). The problem I am having is getting the range move down to the next set of data to copy from worksheet(1) as well as getting the results from worksheet(2) to paste in the row below the last result in worksheet(3).

It seems that the code I have tried doesn't move to new data to copy and that it doesn't allow enough time to pass before copying the result of the equation in worksheet(2). Below is the code I have attempted which doesn't work:

Sub COPY_PASTE_MLR_10()

Application.ScreenUpdating = False

Dim Y As Integer, X As Integer, I As Integer


'COPY DATA TO EQUATIONS

For I = 4 To 255
Y = I
X = I + 10

Sheets("QDATA").Range("G4:G14").COPY _
Destination:=Sheets("R10").Range("A5")

Sheets("QDATA").Range("OI:OI+10").COPY _
Destination:=Sheets("R10").Range("C5")

Sheets("QDATA").Range("WI:WI+10").COPY _
Destination:=Sheets("RLR10").Range("D5")

Sheets("QDATA").Range("AC4:AC14").COPY _
Destination:=Sheets("R10").Range("E5")

Sheets("QDATA").Range("AN4:AN14").COPY _
Destination:=Sheets("R10").Range("F5")

Sheets("QDATA").Range("BA4:BA14").COPY _
Destination:=Sheets("R10").Range("G5")

Sheets("QDATA").Range("BI4:BI14").COPY _
Destination:=Sheets("R10").Range("H5")

Sheets("QDATA").Range("BQ4:BQ4").COPY _
Destination:=Sheets("R10").Range("I5")


'COPY RESULTS TO '10 RESULTS'

Sheets("R10").Range("J5:K5").COPY
Sheets("10 RESULTS").Range("B2:C2").PasteSpecial Paste:=xlPasteValues

Sheets("R10").Range("J6:K6").COPY
Sheets("10 RESULTS").Range("D2:E2").PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True

End Sub

David Zemens David Zemens · Accepted Answer · 2015-04-28T19:06:33

You've got some loop iterator variables but you're not using them correctly. Try this:

For I = 4 To 255
    Y = I
    X = I + 10

    Sheets("QDATA").Range("G" & Y & ":G" & X).COPY _
    Destination:=Sheets("R10").Range("A" & Y+1)

Moving ranges to copy and paste in Excel VBA

1 Answers