Copy and paste a row of data a specified number of times

  • I would like to write a macro that will eliminate some redundant work in our inventory spreadsheet.


    Let's say that I enter info into columns A:G. And let's assume that I sometimes need to duplicate all that info onto the very next line, sometimes multiple lines.


    I would like to have a specific cell somewhere on the worksheet (like cell H1) where the operator types the number of times they want to duplicate the last row of data on the spreadsheet (ex: 5). So, if they type "5" in the cell, and ran the macro, it would take the last row of data and copy/paste it 5 times starting with the next blank row. Is there code that can do this?

  • Re: Copy and paste a row of data a specified number of times


    Have a try with something like this:

    Code
    1. Option Explicit
    2. Sub copy_x()
    3. Dim lr As Long
    4. Dim j As Long
    5. lr = Range("A" & Rows.Count).End(xlUp).Row
    6. For j = 1 To Range("H1").Value
    7. Range("A" & lr & ":G" & lr).Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
    8. Next j
    9. End Sub
  • Re: Copy and paste a row of data a specified number of times


    airwitte,

    Welcome to the Ozgrid forum.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code
    1. Option Explicit
    2. Sub CopyLastRowH1Times()
    3. ' stanleydgromjr, 05/16/2013
    4. ' [URL]http://www.ozgrid.com/forum/showthread.php?t=178651[/URL]
    5. Dim lr As Long
    6. lr = Cells(Rows.Count, 1).End(xlUp).Row
    7. Range("A" & lr & ":G" & lr).Copy Range("A" & lr + 1).Resize(Range("H1").Value)
    8. End Sub



    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the CopyLastRowH1Times macro.