VBA - Copy, Paste then move to next row until reaching blanks

Question

Essentially, I have data in three columns and a model on a separate tab. The data tab has 1,000 rows of data, and each entry will be run through the model, with results being pasted into the fourth column.

Here's what one iteration would look like, but I need it to loop through every row.

Worksheets("Data").Range("E2:G2").Copy _ 
Worksheets("Model").Range("B4:D4").PasteSpecial Paste:=xlPasteValues

Calculate

Worksheets("Model").Range("C120").Copy_
Worksheets("Data").Range("H2").PasteSpecial Paste:=xlPasteValues

Worksheets("Model").Range("C121").Copy_
Worksheets("Data").Range("I2").PasteSpecial Paste:=xlPasteValues

Worksheets("Model").Range("C122").Copy_ 
Worksheets("Data").Range("J2").PasteSpecial Paste:=xlPasteValues

Then we'd copy the next row of data from the Data tab (i.e., range E3:G3).

This seems like a classic loop scenario, but I don't know how to write it in VBA.

Side note: remove those line continuations... `_` should not be anywhere in this code snippet, as `Copy` is one line, and `PasteSpecial` is a new one. — BigBen, Mar 18 '20 at 18:16
Why don't you try something? There is plenty out there on loops. — SJR, Mar 18 '20 at 18:17
Also since you just need the values, and not formatting/formulas, etc. you can set the ranges equal instead of using `.Copy`: `Range([destination range]).Value = Range([original data range]).Value`. Also, [what have you tried](https://www.google.com/search?q=vba+for+loop+until+empty+cell&oq=vba+for+loop+until+empty+cell)? — BruceWayne, Mar 18 '20 at 18:18
Can you add some sample data. It isn't clear if you want to run the model, copy the results then run the model again and copy the same result range out or if you have a sheet with multiple results sets seperated by a blank. — Dan Donoghue, Mar 18 '20 at 23:25

Mech · Answer 1 · 2020-03-19T04:13:42.260

1

This is a simple loop that finds the last row in "Data" and uses it for the loop defined in "Model".

The expected result of this is that the loop will begin at row 120 and continue until the last row in "Data", copying data from C120 through to C(lRow) and pasting it into the "Data" sheet.

Sub test()
    ' declare your variables so vba knows what it is working with
    Dim lRow, i As Long
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim srcws As Worksheet: Set srcws = wb.Worksheets("Data")
    Dim destws As Worksheet: Set destws = wb.Worksheets("Model")

    ' find the last row in Data
    lRow = srcws.Cells(srcws.Rows.Count, 1).End(xlUp).Row

    ' iterate from 120 to the last row found above
    For i = 120 To lRow
        ' copy /paste the data
        srcws.cells(1, 3).Copy Destination:=destws.cells(2, 7 + i)
    Next i
End Sub

edited Mar 19 '20 at 04:13

answered Mar 18 '20 at 18:25

Mech

3,952
2
14
25

1

Read BruceWaynes post above about setting values instead of using Copy/PasteValues. – Frank Ball Mar 18 '20 at 19:17
Couldn't you do `srcws.Cells(1,3).copy` (same with the destination)? Also if you keep `Range()`, you'll want to qualify the `Cells()` inside with the worksheet as well. – BruceWayne Mar 19 '20 at 03:55
regarding the qualify the `cells()`.. would you need to *if* the reference is called on the outside? ie: `srcws.range(cells(1,3))` is the same as saying `srcws.range("C1")`. – Mech Mar 19 '20 at 04:15

Dan Donoghue · Accepted Answer · 2020-03-19T00:48:22.317

You can do this on a range, I see two ways you can do it, using a copy and paste or simply replicating a transposed version of the data:

'Copy and paste method
Worksheets("Model").Range("C120:C" & range("C" & rows.count).end(xlup).row).Copy 'Using the .end(xlup) will find the last row of data without looping until blank.
Worksheets("Data").Range("H2").PasteSpecial xlPasteValues,,,True 'The True here is what tells the pastespecial to transpose

'Transpose method
Worksheets("Data").Range("H2:J2").Value = application.transpose(Worksheets("Model").range("C120:C122"))

Each have their advantage, the Copy and Paste method is easier because you don't need to know the end column so it works easier for a dynamic range, the transpose method doesn't use the clipboard so is less impact on your system.

The better method code wise would be the transpose method.

You can then set up a simple For Next loop to run through as many data ranges as you want.

Dim DataRow As Long, MyDat As Worksheet, MyModel As Worksheet
Set MyDat = Worksheets("Data")
Set MyModel = Worksheet("Model")
For DataRow = 2 To MyDat.Range("E" & Rows.Count).End(xlUp).Row
    MyModel.Range("B4:D4").Value = MyDat.Range("E" & DataRow & ":G" & DataRow).value
    Calculate
    MyDat.Range("H" & DataRow & ":J" & DataRow).Value = Application.Transpose(MyModel.Range("C120:C122"))
Next

Unfortunately I have had to edit the code to introduce a loop due to the dynamic nature of the data with multiple range groups of varying size but I think it will be a decent speed and definitely much better than doing a row at a time. I didn't read the question properly the first time which is why I thought it could be done without a loop — Dan Donoghue, Mar 18 '20 at 23:20

score -2 · Answer 3 · edited Mar 18 '20 at 22:25

Best way is to use the cells-function, where the first argument is the row and the second is the column. Since you want to inrement the source to copy from by one row at a time but increment the paste destination by one column by a time, this method will be suitable.

In addition, try to not use "copy-paste", focus on setting the value for a cell by referring to a the value attribute from the source to copy. Each time you copy and then paste into the destination, you will need an additional memory cell, resulting in a much longer elapsed time if you are working with a large range to copy.

The code below should do the job.

Sub CopyData()
    Dim i As Integer
    i = 8 ' Start pasting into column H
    ' Loop until a blank cell is found
    Do While Not Selection.Value = 0
        With Sheets("Data").Cells(i + 112, 3)
            ' Select each cell in "Data", starting on C120
            .Select
            ' Copy the value into "Model", starting on H2
            Sheets("Model").Cells(2, i).Value = .Value
        End With
    Loop
End Sub

Using .select is an awful approach. It is resource heavy and not recommended. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Mech, Mar 18 '20 at 19:28

VBA - Copy, Paste then move to next row until reaching blanks

3 Answers3