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:
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
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.