none
Copy and Pasting a variable data range RRS feed

  • Question

  • Hello,

    I am very new to VBA and would like to do the following: copy and paste range A4:C16 one two rows below the last row (so row 18 in this case).

    However, when I run the macro next I would like for it to selects the previously pasted range and again copy/paste it 2 rows below that range.

    The amount of rows in that range can vary, however, since sometimes new data is added as rows. So in the first case it would be 13 rows (4-16), but sometimes one of these ranges will expand to 40 rows.

    I would like for the macro to automatically copy and paste the entire range, no matter how many rows there are, 2 lines bellow the previous range.

    How can I accomplish that?

    Thanks!

    Friday, September 1, 2017 7:34 PM

All replies

  • jk,
    re: paste below last row

    Find the last row in the longest column (The example uses Column A)...
       Dim LastRow As Long
       LastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1).End(xlUp).Row
       LastRow = LastRow + 2

    The above code does the same as selecting the last cell in column A and pressing Ctrl + UpArrow

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Friday, September 1, 2017 11:09 PM
    Friday, September 1, 2017 11:08 PM