Copy Formula

[GIngerlomax]

Trying to get my insert new row button to work Any help would be amazing!!

First i select the Row.

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:=1, Type:=1) 'Default for 1 row, type 1 is number
If firstrw = False Then Exit Sub
End If

Cells(firstrw, "A").Select

Then Select the amount of Rows.

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

Then copy down formula from above.

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

But it does not seem to be working. I get an 400 error

 

[GIngerlomax]

It has inserted a new row but not in the correct place and without the formula.

https://www.dropbox.com/s/mnera9astplk7li/Example 2.PNG?dl=0

 

[GIngerlomax]

I have it copying but its copying all the cells not just the one row i wanted. HELP HELP HELP.....

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
End If


Cells(firstrw, 1).EntireRow.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


Selection.EntireRow.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

End Sub

 

[GIngerlomax]

I have run this without the merged cells and it works. I just need a way of getting around the merged cell problem..

 

[GIngerlomax]

Got around the merged cell problem (I asked a friend) but now its not copying. Just inserting a blank row.

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


IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
ws.Range("A" & firstRow + 1 & ":A" & firstRow + Val(vRows)).EntireRow.Insert
ws.Range(ws.Cells(firstRow, 1), ws.Cells(firstRow + Val(vRows), IngA)).FillDown




End Sub