Insert row and copy function/format from specific row

kitvel · Sep 19th 2017

HI,

Trying to create a macro that will give the user a box asking for how many rows he/she wants to insert, and once the rows are inserted the should have(copied) the functions/format from a specific row, in my case row 2.

The box asking for rows is done, works like a charm, I have start and end cells so the user can only insert rows within a specific range, and all this works. But the rows inserted have the same format as the row above, and doesn't copy the functions.

So what I want is that once you insert your rows they should always have the same function, formulas, format, conditional formatting as row2 (A2:J2 to be specific).

My code for inserting row:

Code

Sub InsertRow()
Dim intRownr As Integer
Dim intStartrow As Integer
Dim intEndrow As Integer
Dim Box As Integer
Dim Row As Integer
intRownr = ActiveCell.Row
intStartrow = ActiveSheet.Range("First").Row + 1
intÉndrow = ActiveSheet.Range("Last").Row + 1
If intRownr < intStartrow Or intRownr >= intEndrow Then
MsgBox "Can't insert here, please mark " & intStartrad & " and row " & intSlutrad - 1 & "." & Chr(13) & "New row bla bla bla.", vbOKOnly, "bla bla bla"
Exit Sub
End If
On Error Resume Next
Box = InputBox("How many rows?")
On Error GoTo 0
For Row = 1 To Box
Selection.EntireRow.Insert
Next Row
End Sub

Display More

I guess I need som copy/paste row2 after the "Selection.EntireRow.Insert" statement, but can't get it to work. All help very appreciated.

Share