Insert new row with all Formule

[GIngerlomax]

Hi there any help will be greatly appreciated.

I need to add a new Row so that it copies all formula and formats from above. Is there a way of making it auto so when they select "Insert new row" the macro automatically does it?

I have found some code on here to make it work via a button but nothing seem to happen?

Sub Loop_InsertRowsandFormulas()


Dim vRows As Long
Dim firstrw As Long
If firstrw = 0 Then
firstrw = Application.InputBox(prompt:="Indicate under which row you want the macro to begin inserting rows. " _
, Title:="Start Row", Default:=7, Type:=1) 'Default for 1 row, type 1 is number
If firstrw = False Then Exit Sub
End If


Cells(firstrw, "A").Select


ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:="How many rows do you want to add? ", Title:="Add Rows", Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If


Do Until ActiveCell.Value = ""
ActiveCell.EntireRow.Select
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.Resize(vRows, Selection.Columns.Count - 1).Offset(1, 1).SpecialCells(xlCellTypeConstants).ClearContents
ActiveCell.Offset(vRows + 1, 0).Activate
Loop
End Sub

...

 

[GIngerlomax]

Can anyone help me. I have a feeling its to do with the 1st 12 rows and the last 4 as they are merged cells..

 

[GIngerlomax]

Help Help Help!!!

I have taken out

Dim vRows As Long
Dim firstrw As Long
If firstrw = 0 Then
firstrw = Application.InputBox(prompt:="Indicate under which row you want the macro to begin inserting rows. " _
, Title:="Start Row", Default:=7, Type:=1) 'Default for 1 row, type 1 is number
If firstrw = False Then Exit Sub
End If


Cells(firstrw, "A").Select


ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:="How many rows do you want to add? ", Title:="Add Rows", Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If

I would like to just select the Row then either rick click and insert row or then press the button what bit of code do i need to replace it with....

 

[GIngerlomax]

Could i just put it into the right click option

 

[GIngerlomax]

We got it working!!

Sub Loop_InsertRowsandFormulas()


Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Risk Input Sheet")
Dim vRows As Long
Dim lastCol As Long
Dim firstRow As Long


firstRow = InputBox("Enter Row To Start Insert From.")
vRows = InputBox("Enter Number Of Rows Required")


If firstRow = 0 Or vRows = 0 Then Exit Sub
Debug.Print firstRow
IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
For myLoop = 1 To vRows
ws.Range("A" & (firstRow + myLoop)).EntireRow.Insert
ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop)).Value = ws.Range("A" & firstRow & ":BB" & firstRow).Value
Next


End Sub

 

[GIngerlomax]

The one above had a problem with the formula. This one works much better with the mearged rows. Thanks Dave

Sub Loop_InsertRowsandFormulas()


Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Risk Input Sheet")
Dim vRows As Long
Dim lastCol As Long
Dim firstRow As Long


firstRow = InputBox("Enter Row To Start Insert From.")
vRows = InputBox("Enter Number Of Rows Required")


If firstRow = 0 Or vRows = 0 Then Exit Sub
Debug.Print firstRow
IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
For Myloop = 1 To vRows
ws.Range("A" & (firstRow + Myloop)).EntireRow.Insert
ws.Range("N" & (firstRow) & ":AW" & (firstRow + Myloop)).FillDown
Next


End Sub