Insert row with template row of formulas

[rjplante]

I want to use row 4 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 no 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

[Paul_Hossler]

I selected B9 so that is the ActiveCell

Running the macro, makes a copy of Row 4 in row 9 and a copy in row 10

What had been the active cell row (i.e. row 9) is now row 11






Option Explicit


Sub Macro1()
Dim rActiveCell As Range


Range("B9").Select ' for testing - I keep forgetting to manually select it -- delete the line

Application.EnableEvents = False


Set rActiveCell = ActiveCell.Cells(1, 1).EntireRow


ActiveSheet.Rows("4:4").Copy
rActiveCell.Insert Shift:=xlDown
ActiveSheet.Rows("4:4").Copy
rActiveCell.Insert Shift:=xlDown
Application.CutCopyMode = False

Application.EnableEvents = True


End Sub

[大灰狼1976]

Sub Insert_New_Row()
Application.EnableEvents = False
Dim MyRow As Range
Set MyRow = Rows(4).EntireRow
MyRow.Copy
ActiveCell.Resize(4).EntireRow.Insert Shift:=xlDown, CopyOrigin:=MyRow
Application.EnableEvents = True
End Sub

[rjplante]

Thanks for the help. I thought I was close, just needed a couple more pieces to the puzzle to get it figured out. It is working perfectly now.




Sub Insert_New_Row()
Application.EnableEvents = False
Dim MyRow As Range
Set MyRow = Rows(4).EntireRow
MyRow.Copy
ActiveCell.Resize(4).EntireRow.Insert Shift:=xlDown, CopyOrigin:=MyRow
Application.EnableEvents = True
End Sub