Need a way to copy and paste previous rows after a dropdown selection is made on the same sheet

[strlok]

Hello, I think I need to use a Worksheet Change macro but I am not entirely sure. What I'm looking to do is simple. When someone selects a value from the dropdown in cell C5, I need it to copy and paste rows 1 to 10 to row 13. But I need this to occur indefinitely as long as any value in the "USER ID" dropdown is selected so I can have multiple of the same row.

1) Here is what my spreadsheet looks like

2) Here is what happens when I select a value in the dropdown of C5

3) But what I want after a value is selected in C5 I want it to copy and paste that whole section and provide a blank one (with all the formulas) like this:

And then if a value is selected in cell C17, it copies and pastes in row 25, and so forth. I need this to be indefinite.

Just not sure how to create the macro to get this to work.

Thank you for the help.

 

[Candyman8019]

What I would typically do in this scenario is create a section at the top of your form and hide it. For example rows 1-7 could contain your blank template with formulas, formatting, color, etc. Hide them. Next, you would have your headings, ie case#, item#, etc.

Then add a change event like this. It will essentially copy rows 1-7 and paste them two rows below the last section.

Private Sub worksheet_change(ByVal target As Range)
Dim lrow As Long

If Not Intersect(target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False

'find last row
lrow = Sheet11.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 2
Range("1:7").Copy Destination:=Range("A" & lrow)

Application.EnableEvents = True
End If
End Sub

 

[Candyman8019]

small change to the VBA...

Private Sub worksheet_change(ByVal target As Range)
If target.CountLarge > 1 Then Exit Sub
Dim lrow As Long

If Not Intersect(target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False

'find last row
lrow = Sheet11.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 2

'copy hidden range.  Paste after last row.
Range("1:7").Copy Destination:=Range("A" & lrow)

Application.EnableEvents = True
End If
End Sub

 

[strlok]

Hello Candyman,

Thank you very much for your time and effort with my question.

So I took your code and modified the range to be "1:10", hid the 10 rows...but when I went to select the dropdown, it's not copying and pasting the hidden rows. I tried just your original code with "1:7" and hiding 7 rows but that didn't work either.

 

[Candyman8019]

ah yes...we need to temporarily unhide the rows before copying them. Give this a try.

Private Sub worksheet_change(ByVal target As Range)
If target.CountLarge > 1 Then Exit Sub
Dim lrow As Long

If Not Intersect(target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False

'find last row
lrow = Sheet11.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 2

'copy hidden range.  Paste after last row.
Range("1:10").EntireRow.Hidden = False
Range("1:10").Copy Destination:=Range("A" & lrow)
Range("1:10").EntireRow.Hidden = True

Application.EnableEvents = True
End If
End Sub

 

[strlok]

Ok I tried that but its not working when I select a dropdown. I am not sure if this part of the code is correct "Sheet11<span>.</span>Cells<span>.</span>Find" shouldn't this be Sheet1? I tried changing that too but same result.

To be clear, I am hiding rows 1-10, then I have a section starting at row 11 to row 20 where I can enter in the the data, then when I click on the dropdown for C:15 and select a value, I need it to past the row 1-10 template into row 23-31 (which I have highlighted in the screenshot below).

I think your VBA code looks like what I'm after here, so I am not sure what I'm doing wrong.

 

[Candyman8019]

Yes. Absolutely, the sheet reference should be whatever sheet you're working on. Also, to clarify, this code is in the specific sheet...not a module, correct? If you step through the code with F8, can you determine what step isn't working for you? Also, if you've had the code stop partway through, you'll need to re-enable events for the worksheet_change to fire again.

Sub RestartEvents()
Application.EnableEvents = True
End Sub

 

[strlok]

Ok I think that was it, enabling events but I have another problem (And yes the code is in the specific sheet) But now the cells are copying and pasting over previous entries. It's as if its pasting right into row 16 instead of it needing to be lower. I'm not sure which part of the code I would need to fix that, the +2?

 

[Candyman8019]

In your hidden rows put a space in the last row of yellow cells. I suspect it’s not finding the last row because they’re just colored with no values.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.