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.

 

[strlok]

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.

Thank you, this fixed it.

Is there a way I can just target the "UserId" dropdown cell to create a new template beneath instead of the range "C:C"? Reason being is that I don't want a new template to be created if any other cell in that column has information in it.

 

[strlok]

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.

I'm sorry about that. I will keep this in mind for the future. Thank you for telling me this.

 

[Candyman8019]

Thank you, this fixed it.

Is there a way I can just target the "UserId" dropdown cell to create a new template beneath instead of the range "C:C"? Reason being is that I don't want a new template to be created if any other cell in that column has information in it.

There's likely a better way, but give this a try.

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

If Not Intersect(target, Range("C:C")) Is Nothing Then
On Error GoTo BypassCopyRange
x = target.Validation.Formula1

Application.EnableEvents = False

'find last row
lrow = Sheet1.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
BypassCopyRange:
End Sub