VBA code to copy formula from Previous row when inserting a new row using a command button

  • Hi All,

    I am hoping that someone can help me, I am creating a spreadsheet which has a couple of easy formulas and a few cells that use data validation.

    I have set up a command button to insert a new row below the previous row and I want to be able to one copy the formulas and two if possible the data validation into the new row whilst keeping the original data in the previous row.

    This is the code I have for the command button, I have tried various codes to also copy the formulas but nothing is working.

    1. Private Sub CommandButton1_Click()
    2. Dim rowNum As Integer
    3. On Error Resume Next
    4. rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
    5. Title:="Insert Quote Row", Type:=1)
    6. Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
    7. End Sub

    I would be really grateful if anyone could assist me in this matter. :)

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    That's like reinventing the wheel! If you use Excel's Table feature then that will be automatic.

    Overview of Excel tables