Insert row with template row of formulas

[rjplante]

I have the code listed below which will insert a blank row until the second row of my selected range. I want to be able to use a template row of formulas to be inserted in each instance. I need to know what the correct code is for step 4 to make sure that all my formulas get added to the new rows.

Row 4 of my spreadsheet is to be my template row and I don't know if I need to define MyRow as:

1) MyRow = Rows(4).EntireRow
2) MyRow = Rows(4)
3) Set MyRow = Rows(4).EntireRow
4) Set MyRow = Rows(4)

Thanks

'Step1:  Declare all of the needed variables.
    Dim MyRow As Long
    Dim MyRange As Range
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim iCounter As Long
    
'Step 2:  Define the variables.
    MyRow = Rows(4).EntireRow
    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    Set MyRange = Rows("4:" & LastRow)
    
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 2 Step -1
    
'Step 4: Insert blank row.
    MyRange.Rows(iCounter).EntireRow.Insert

 

[rjplante]

Would it work to use the insert code as listed below?

'Step 4: Insert blank row.
    MyRange.Rows(iCounter).EntireRow.Insert Shift:=xlDown, CopyOrigin:=MyRow

 

[rjplante]

I have distilled my macro down to just the basics of what I need to accomplish. I want to use row for as my template row (with all the necessary formulas), and insert two rows at the position of my active cell. My code is below. When I run it it will insert only one row and now formulas. What do I need to do to fix this?

Thanks

Sub Insert_New_Row()

Application.EnableEvents = False

    Dim MyRow As Range

    Set MyRow = Rows(4).EntireRow
    
    ActiveCell.Rows(2).EntireRow.Insert Shift:=xlDown, CopyOrigin:=MyRow

Application.EnableEvents = True

End Sub