How to create macro for adding 1 section at a time

[sdylanh]

Good day to all,

Excel amateur here. I'm trying to create a spreadsheet to serve as an input form for recording data within my company. I essentially have a block of cells from A3:M7 that represent a single transaction input, and the end user will need to do this anywhere from a couple of times a day to 30 times a day.

What I want to create is a somewhat minimalist reproduction of this block of cells depending on the users need. This is essentially a form, but for now I do not want to create an actual form as I want it embedded in the spreadsheet as its being entered.

Two primary questions:

1.) How can I add something like an "Add New" button that reproduces that block of cells each time the "button" is clicked; and
2.) How could I add a numerical drop down if the user knew up front how many reproductions of that block she would need? For instance, if for that day or week she knew she had to input that set of data 30 times, could she choose 30 from a drop down list or type 30 into a specified cell which would then generate 30 reproductions of that block of cells?

Thanks in advance for any help!

 

[Mike LH]

Hi,

This sounds simple enough but I think we need some more information.

1. What are we copying in A3:M7. Is it values, formats, both or something else.
2. When we copy this range then where do we copy it to?
3. If we're copying the range multiple times then where do we place each copy?

 

[sdylanh]

Good questions, thanks for your interest in helping.

1. Formats, including a couple of cells with dropdown lists, and some formulas. Now that you ask, I realize that I'm asking to "copy" but I'd like to clear the contents of certain cells with each copy while retaining the text of certain cells with each copy as well, assuming that's possible.
2. I would like to copy the range directly below the most recently completed set. Ideally, each copy would move the "button" along with it. For instance, I'd love to have a button that says "Add New" in cell N7, and once clicked, my original A3:M7 range gets copied to A8:M12 with a new button now located in N12.
3. I'm guessing this question relates more to my original question 2 than question 1. In this case, if the user chose 30 inputs, I'd like the 30 to be arranged vertically with no spacing. A3:M7, followed by A8:M12, A13:M17, etc.

Let me know if I need to provide more clarification.

 

[Mike LH]

Hi,

The only problem I have with this is finding which row to paste the new frames into. To do this I have made the assumption that at least one cell of the last row of the bottom frame of cells has something in it. If A3:M7 is the only one on the sheet then this assumes row 7 has at least one value. Oh and one other thing you never told me which cells to clear.

Put a button on your worksheet and assign the code below. Enter the number of frames to copy into Q1. If Q1 is empty it will make one copy only.

Sub copy_me()
Dim src As Range, x As Long
Dim lastrow As Long
Set src = ActiveSheet.Range("A3:M7")
For x = 1 To WorksheetFunction.Max(1, Range("Q1").Value)
lastrow = Columns("A:M").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
src.copy
Cells(lastrow, "A").PasteSpecial Paste:=xlPasteAll
Next
End Sub

 

[sdylanh]

Thanks again--haven't tried your code yet, but I'm going to go ahead and answer which cells to clear. Hopefully this doesn't come across too confusing:

To be cleared with each new copy:
A3
B3 (contains a dropdown list which I'd want to copy over)
C3 (contains a dropdown list which I'd want to copy over)
C5
D3
E3
F3:H6
J3:K6

Also, these cells contain formulas that I'd want copied over, but the text cleared with each copy:
E4:E6
I7
L7
M3:M6

All other cells are either blank or contain text that will remain unchanged with each copy. Several of these cells also have data validation limits--would these need to be included in the macro as well? For instance, D3, E3, and C4 need to be limited to whole numbers only between 0 and 99999. F3:H6 and J3:K6 would need to be limited to decimals between 0 and 99999.