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

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

samztheman

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Sorry, here are the images I meant to include
 

Attachments

  • Template pic.png
    Template pic.png
    24.5 KB · Views: 6
  • copied row.png
    copied row.png
    16.8 KB · Views: 6

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

samztheman

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,228
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top