Private Sub CommandButton1_Click()
     Dim rowNum As Integer
     
'don't let Excel tell me about errors
'On Error Resume Next

'get rowNum to deal with
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                Title:="Insert Quote Row", Type:=1)

'incase user closes, cancels or doesn't enter a row number
If rowNum = 0 Then Exit Sub

'prevent screen flash
Application.ScreenUpdating = False

'insert new row
Rows(rowNum).Insert Shift:=xlDown

'copy original row
Rows(rowNum + 1).Copy

'paste formulas of original row
Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False

'paste formatting of original row
Rows(rowNum).PasteSpecial Paste:=xlPasteFormats

'clear cells with constants in newly inserted row
Cells(rowNum, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

'stop the marching ants
Application.CutCopyMode = False

'remove selection from entire new row
Range("A" & rowNum).Select

'turn screen updating back on
Application.ScreenUpdating = True

End Sub