Add a row, copy formulas, and formatting

[sp135]

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:

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:

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

[Leith Ross]

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 .

[teylyn]

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.

[Leith Ross]

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"?

[sp135]

Hi again. Thanks for your time and help, my brain is swimming in macros.

Simply put, I have worksheet #1 that other users are going to enter basic data into, there are 8 columns across, with the first column being the item number, the other cells across are simple data entry. The number of items the users have will vary depending on the specific customer. The current worksheet has ten rows (therefore, up to 10 items) already set up with the respective formulas/formatting. The boss now wants 1 row, and allow the users to add as many rows as they need rather than having a fixed set of 10.

Yes, I need to have the cells in each respective row copy the formulas from the previous row (but not any data that may have been input). Some of the cells in the row are drop down lists and some are standard math formulas.

Yes, the sequence numbers would be in Column A. The existing row would be #1, and this is in cell A8.

Thanks a million. I'm really feeling at a loss with this.

[Leith Ross]

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

[mikerickson]

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