Thread: Add a row, copy formulas, and formatting

I'm new to this forum and new to Excel Macros/VBA and am totally frustrated.

In my Excel spreadsheet I want to have a button for other users to click that will (1) add a new row in a specific location (right now the code I have will add a new row anywhere the user clicks), (2) copy the formulas (but not the data that has been entered), (3) copy the formatting and (4) number the items in the far left column increasing by 1 each time a new row is added.

I can find various vba for inserting a new row (but not in a specified spot), I can find how to add numbers by 1 in rows and I have found vba code for copying formatting. But not all in one code so that the 1 action of clicking the button will perform these 4 functions.

Any help would be so greatly appreciated. Thanks.

The code for adding the new row that I have (but will insert it where ever the user has clicked) is:

Code:

Sub InsertRow()
    Dim Rng, n As Long, k As Long
    Application.ScreenUpdating = False
    Rng = InputBox("Enter number of rows required.")
    If Rng = "" Then Exit Sub
     Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
    'need To know how many  formulas To copy down.
    'Assumesfrom A over To last entry In row.
    k = ActiveCell.Offset(-1, 0).Row
    n = Cells(k, 256).End(xlToLeft).Column
    Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End Sub

However, I found this formula in this forum, but it copies the data the user may have input:

Code:

Sub InsertRow(NumRow As Long)
  Dim RowStr As String
  
    RowStr = CStr(Selection.Row) & ":" & NumRow + Selection.Row - 1
    Selection.EntireRow.Copy
    Rows(RowStr).Insert Shift:=xlDown
    Application.CutCopyMode = False
    
End Sub

Hello sp135,

Welcome to the Forum!

Your post title should convey a brief description of what you need help with. Please do not use general words and phrases like "Help!", "Code doesn't work", "Urgent!", "Should be an easy question", "Is this possible?", etc. Your present title doesn't tell anyone what your question is or what type of problems you are experiencing.
A better title would be "Add a row, copy data, formulas, and formatting".

As a new member, please take the time to read over the Forum Rules .

Hi sp35,

welcome to the forum. Please take a look at the forum rules, then amend your thread title and add code tags.

thanks

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hello sp135,

Thanks for changing the title. I added the code tags to your post so you can see what Teylyn was referring to. Please do this in future posts.

Do you need to copy formulae down once the rows are added? Will the sequence numbers be in column "A"?

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.

Code:

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)

Code:

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