VBA to Copy Paste Multiple Rows from 1 sheet to another sheet, under a specific row containing a text. Be able to do many times.

[samztheman]

Hey, I'm having a lot of trouble copying multiple rows from one sheet to paste under a specific row in another sheet. (but the row # is not static, hence why I want to use a text condition "Deliverables"
Basically, I want to be able to build a macro button that will add these rows whenever the user clicks it, so it can just continually add those rows to their hearts desire. Its important that it can do it multiple times, and always add the new copied rows under the text conditioned row.


I'm able to do a single row with:

Sheets("Sheet1").Rows(5).EntireRow.Copy Sheets("Template").Cells(Sheets("Template").Range("A:M").Find("Deliverables", LookIn:=xlValues, lookat:=xlWhole).Row + 1, 1)

However, it only copies 1 row, can't stack it by just copying the code but changing row 5 to say row 4, and it can't be selected multiple times(it only adds it once, no looping)

To recap, I want to be able to :
1) Copies 5 rows from sheet 1.
2) Always Inserts rows below a specific row in Template sheet, containing the cell value "Deliverables".
3) Keep all existing formatting and the data validation list I have in that( I assume that if its a direct copy/paste, it will keep the formatting and drop-down list.
4) Be able to run macro and it continually adds these rows, even if I add a ton of rows above the deliverables section.

I've seen similar questions on other threads, but they don't quite do what I'm looking for(copy/paste a specific set of rows, onto another worksheet, and always under the row with the cell containing the value "Deliverables", and nowhere else.

Trying to piecemeal existing code from threads hasn't fully worked. The closest I got was one that would copy/paste only one row, but then it can't be looped.
I have the code to do what I want in a static sheet(Where i am not manually adding rows above).

Below are an image of the sheet I am copying to, and an image of the rows I am trying to copy/paste.


Thank you so much for anyone able to answer, total lifesaver.

 

[samztheman]

Sorry, here are the images I meant to include

 

[JLGWhiz]

It is not clear which rows you want to copy nor from which sheet.

What are the sheet names, source, destination?
What rows on the source sheet will be copied, and are the alway the same 5 rows?
You mentioned looping. Where would you expect to loop the copy/paste action and what criteria would you use to limit the iterations of a loop?

Taking what I see, this would copy the fire rows and put them under "Deliverables" row.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveSheet
Set sh2 = Sheets(2) 'Substitute sheet names with actual
sh2.UsedRange.Find("Deliverables", , xlValues, xlWhole).Offset(1).Resize(5).EntireRow.Insert
sh1.Rows("1:5").Copy
sh2.UsedRange.Find("Deliverables", , xlValues, xlWhole).Offset(1).End(xlToLeft).PasteSpecial xlPasteAll
End Sub

 

[samztheman]

Hello, I added screenshots of the files i am working with. They are in my reply to my first post. As for the sheets, I supplied screenshots.
First one(called Template) is the sheet i am copying to. the second image is the sheet i am copying the 5 rows from. The first five rows from source sheet(Sheet 1) is trying to be copied to destination sheet(Template). They are always the same 5 rows.
By looping, i mean that everytime i run the macro, it will copy those same 5 rows, even though they are mostly blank. I just want the format and basic desciprtions in the cells.

 

[JLGWhiz]

Set sh1 = ActiveSheet
Set sh2 = Sheets(2) 'Substitute sheet names with actual

Then if you change the two statements above to reflect the actual sheet names the code should do what you want.

Example:

Set sh1 = Sheets("Copy from")
Set sh2 = Sheets("Template")