Macro Button that when clicked will copy range of cells and paste to next blank row of another worksheet

Question

I have a PAYMENT column and a MASTER LIST OF PAYMENT column in my spreadsheet. PAYMENT column is in INPUT worksheet while MASTER LIST column is in CASHFLOW worksheet. The idea is to copy the range of inputs from the PAYMENT column to the MASTER LIST column, pasting the next BLANK ROW of the MASTER LIST column.

For example: 3 payments were made, data is in A1:A3. Copy A1:A3 to MASTER LIST column A8:A10. Again, 3 payments were made. But since MASTER LIST column A8:A10 already has previous data. This time the data will be pasted to A11:A13. and so on.

So far, I have made the following code but I succeeded in only copy and pasting one row (the first row of the range). I tried editing it to copy range of cells, but it gives me an error. Any suggestions? Thank you!

Sub PAYMENTS_TRANSFER()
Response = MsgBox("Are you sure?", vbYesNo)
If Response = vbNo Then Exit Sub
Dim nextrow As Long
nextrow = Worksheets("Cashflow").Cells(Rows.Count, "A").End(xlUp).Row + 1
If nextrow < 8 Then nextrow = 8
Worksheets("Cashflow").Range("A" & nextrow).Value = Worksheets("Input").Range("A1").Value
End Sub

Get the last used row number from MASTER LIST column and paste your values accordingly. — ManishChristian
Is the Payment worksheet a dynamic list like the Master List worksheet? — Rafael Matos
@ManishChristian Hi, I want the VBA to paste it automatically as my data is very large, making it tedious to paste manually. — Fritze Ong
@RafaelMatos Hi. No, the Payment column in my spreadsheet is static, in actual it is from B73:B82 always. In the case of my example above, it is always A1:A3. But the data that will be inputted there may not always be the same. Like sometimes only 1 data is inputted, other times there are 3-4 data, etc. — Fritze Ong

mooseman mooseman · Accepted Answer · 2017-08-28T16:51:49

You are not copying the full range since you don't have the bottom row number from the Sheet("Input"). Try this:

    Sub PAYMENTS_TRANSFER()
    Response = MsgBox("Are you sure?", vbYesNo)
    If Response = vbNo Then Exit Sub
    Dim nextrow As Long
    nextrow = Worksheets("Cashflow").Cells(Rows.Count, "A").End(xlUp).Row + 1
    lastrow = Worksheets("Input").Cells(Rows.Count, "A").End(xlUp).Row
    If nextrow < 8 Then nextrow = 8
    Worksheets("Cashflow").Range("A" & nextrow).Value = Worksheets("Input").Range("A1:A" & lastrow).Value
    End Sub

Macro Button that when clicked will copy range of cells and paste to next blank row of another worksheet

1 Answers