Add a row, copy formulas, and formatting

Hello sp135,

I have created an example workbook. This has a button below the first entry on row 8. Clicking the button will add a new row. There are named ranges on the sheet "Named Ranges" for the drop down validation and the starting sequence number. This is computed by subtracting 7 from the current entry row. Since the first entry row is 8, the line item number equals 1. Here is the macro called by the button.

Sub AddRow()

  Dim Cell As Range
  Dim CmdBtn As OLEObject
  Dim Rng As Range
  
    Set CmdBtn = ActiveSheet.OLEObjects("CommandButton1")
    Set Rng = CmdBtn.TopLeftCell.Offset(-1, 0).Resize(1, 7)
    
      CmdBtn.Top = CmdBtn.TopLeftCell.Offset(1, 0).Top + 3
      Rng.Copy Rng.Offset(1, 0)
        
      For Each Cell In Rng.Offset(1, 0)
        If Not Cell.HasFormula Then Cell = ""
      Next Cell
      
End Sub

This worked for me. (Although I had to make my own test book. My Mac doesn't play well with ActiveX controls)

Sub MakeNewRows()
    Dim rowsToAdd As Long
    Dim LastCell As Range, newRange As Range
    rowsToAdd = Application.InputBox("How many", Default:="1", Type:=1)
    If 0 < rowsToAdd Then
        Rem cancel not pressed
        With ThisWorkbook.Sheets("Entry Form").Range("A:A")
            Set LastCell = .Cells(.Rows.Count, 1).End(xlUp)
        End With
        With LastCell.Resize(rowsToAdd + 1, 1).EntireRow
            .FillDown
            On Error Resume Next
            .Offset(1, 0).SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0
        End With
    End If
End Sub