Forum Discussion

NotExcellentUser's avatar
NotExcellentUser
Copper Contributor
Sep 03, 2024

VBA macro to copy & insert new row

I need to build a button-controlled macro to copy a few rows and insert them directly beneath the copied rows. The complicated part is that this needs to be able to repeat itself and move the selection below the previous repeated rows, and I need this action to be repeated on two sheets simultaneously. An example of my desired actions is:

 

User has a workbook and the "Working Papers" sheet selected. The user selects the "New Rows" button for the first time. Rows 3:26 of the Working Papers sheet are copied and inserted beneath row 26 of the Working Papers sheet. Simultaneously, rows 2:26 of the "Backup" sheet are copied and inserted beneath row 26 of the Backup sheet.

The user clicks the New Rows button again. Rows 27:50 of the Working Papers sheet are copied and inserted beneath row 50 of the Working Papers sheet. Simultaneously, rows 27:50 of the Backup sheet are copied and inserted beneath row 50 of the Backup sheet.

 

This action may be repeated infinite times. My biggest confusion is how to ensure that the macro selects the last inserted rows and inserts the new rows beneath them. Any advice or guidance is appreciated. If there is a snippet of VBA code that accomplishes something like this would be happy to adapt it to my situation.

excel
Macros and VBA

1 Reply

Sort By
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Sep 04, 2024

    Hello NotExcellentUser 

     

    Sub NewRows()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim rng1 As Range, rng2 As Range
    
    ' Define your sheets
    Set ws1 = ThisWorkbook.Sheets("Working Papers")
    Set ws2 = ThisWorkbook.Sheets("Backup")
    
    ' Find the last row with data in the sheets
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Define the ranges to copy
    Set rng1 = ws1.Rows(lastRow1 - 23 & ":" & lastRow1)
    Set rng2 = ws2.Rows(lastRow2 - 24 & ":" & lastRow2)
    
    ' Insert the copied rows below the last row
    rng1.Copy
    ws1.Rows(lastRow1 + 1).Insert Shift:=xlDown
    
    rng2.Copy
    ws2.Rows(lastRow2 + 1).Insert Shift:=xlDown
    
    ' Clear any cut/copied selection to avoid issues
    Application.CutCopyMode = False
End Sub

     

    This macro was generated by an AI based on your query; however, it has not been tested.

    Explanation:

    1. Sheets: The code assumes that your "Working Papers" and "Backup" sheets are already set up. Adjust the names if your sheets have different names.
    2. Last Row Calculation: The code calculates the last row with data in both sheets.
    3. Ranges to Copy: The ranges rng1 and rng2 are defined based on the last row of data and are then copied.
    4. Insertion: The code inserts the copied rows directly beneath the last row in both sheets.
    5. Repetition: Each time you run the macro, it will find the new last row and repeat the process.

    How to Set Up the Button:

    1. Go to the Developer tab in Excel.
    2. Click on Insert and select a Button.
    3. Draw the button on your "Working Papers" sheet.
    4. Assign the NewRows macro to the button.

    Repetition:
    The macro automatically adjusts the copied rows to the most recent set, allowing infinite repetition.

Resources