How to ask the user How many line items? and automatically create/insert rows

[derekneufeld]

Hello everyone,


I have made a protected template for an invoice coversheet for my co-workers to use to generate invoices. However, you need to manually insert rows for each line item.


It starts with three pre-formatted line item rows available and I have removed protection from those three rows so that one can highlight the row(s), insert x rows, highlight the top row, then drag down the format to the new rows. The three pre-made rows are rows 26-28. There is a SUM formula in merged cell V29:Z29 that sums dollars in merged cells above it: =SUM(W26:Z28).


What I am hoping to find out is if there is a way that I can have another user (an excel beginner) have an easy way to do this (i.e. a macro or an insert row formula). This was never a big issue until we had a new person come in that can't seem to retain the coaching I give every time. Nice guy, but lacks an excel mindset. So if anyone knows how to create a button (or something similar) where all he has to to is click the button, excel will ask him, "How many line items?", he can type "7" and it will create/insert a total of 7 pre formated rows starting at row 26 but still retaining the sum formula below all of the line items.


Any thoughts?


Any help would be greatly appreciated.


Derek

 

[Luke M]

Welcome to the forums!


Here's a short macro that might get you started:

Sub InsertRows()

Dim UserChoice As Integer

Dim NewRows As Integer


'Ask for input

On Error Resume Next

Do

UserChoice = InputBox("How many rows would you like to add?", "Insert Rows")

Loop Until UserChoice > 0

On Error GoTo 0

NewRows = 26 + UserChoice

Rows("26:26").Copy

Rows("27:" & NewRows).Insert Shift:=xlDown

Application.CutCopyMode = False

End Sub

 

[derekneufeld]

Thanks! I'll give it a try.