I need to create a button so that it will create a new row with th existing row properties


I wanted a vba code to create a new row with all the functionalities of the previous rows.

If ossible please help



Do you mean to copy/paste them? What functionalities are you talking about - formulas and functions, conditional formatting, data validation, security settings (locking), etc?
don (rep: 1969) Jul 28, '17 at 7:11 am
Add to Discussion



This might do the job.

Sub InsertRowBelow()
    Dim Rng As Range
    Dim R As Long
    R = Selection.Row
    With ActiveSheet
        Set Rng = .Range(.Cells(R, 1), .Cells(R, .UsedRange.Columns.Count))
        .Rows(R + 1).Insert
    End With
    With Rng
        .Copy Destination:=.Cells(1).Offset(1)
        On Error Resume Next                ' if no constants were found
    End With
    Application.CutCopyMode = False
End Sub

The code will insert a row below the selected row (first selected row if many are selected). It will then copy everything from the selected row to the new row. Note that this action will fail if there are vertically merged cells in the selected row. Finally, it will remove all values copied from the original, but keep all formulas.

You can link this code to a button on your sheet or, as I usually do it, link it to the double-click event on a particular cell, such as the first empty cell at the bottom of column A.


I used the following macro last year to achieve the same result.

Sub Add_Row()

If MsgBox("Is there more than one blank row at bottom ?", vbYesNo + vbExclamation + vbDefaultButton1, _
"Add a new row ?") = vbYes Then Exit Sub

ActiveSheet.Unprotect   ' required for macro to run
' find the last row with data
Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
ActiveCell.Offset(1).Select     ' makes the first empty cell (below the last cell with data) the active cell
        Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 12)).Select     ' cells A thru L of first blank row
            Selection.Copy     'copies cells A thru L of first blank row
            ActiveCell.Offset(1).Select     ' makes the cell below the active cell
            ActiveSheet.Paste                 ' pastes the copied A thru L cells
            Application.CutCopyMode = False     ' exits cut/copy mode
            ActiveCell.Offset(-1).Select     ' makes the cell above the active cell
                ActiveCell.Comment.Delete     ' removes comment from cell
                ActiveCell.Validation.Delete     ' removes data validation rule from cell

ActiveSheet.Protect     ' to prevent sheet formulas from becoming corrupted
Application.SendKeys "{NUMLOCK}"    ' keeps NUMLOCK turned ON
Application.SendKeys "%H%"  ' this selects the HOME tab of the ribbon

End Sub


Loving the commenting in the macros! Sometimes it's easy to forget that.
don (rep: 1969) Aug 7, '18 at 2:50 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login