Copy x rows from a worksheet and paste x rows into another worksheet n times [Solved]

Posts
7
Registration date
Thursday April 11, 2019
Status
Member
Last seen
April 25, 2019
- - Latest reply: TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
- Apr 29, 2019 at 11:16 AM
Hi,
I am struggling to create a macro that can copy an excel range of data (A1:B50) from sheet 1 and paste into sheet 2 for 4000 times. Currently, sheet 2 contains a column of data with 4000 rows. Hence, i am trying to insert and paste (A1:B50) from sheet 1 into each row in sheet 2. it should visually look like this:

Sheet 1(A1:B50)
1 Cat
2 Dog
.
.
.
49 Bird

Sheet 2
Row# Name
1 Mr Tom
2 Mr Pat
.
.
.
4000 Ms Mary
The final version should be

Sheet 2
# Name
Row1 Mr Tom
1 Cat
2 Dog
.
.
.
49 Bird

Row 2 Mr Pat
1 Cat
2 Dog
.
.
.
49 Bird
.
.
.

Row 4000 Ms Mary
1 Cat
2 Dog
.
.
.
49 Bird
See more 

3 replies

Best answer
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
377
1
Thank you
Hi Christol,

Sheet1 Data:
1 Cat
2 Dog
3 Mouse
4 Lion

Sheet2 Data:
1 Tom
2 Pat
3 Mike
4 Hank

Running the following code:
Sub RunMe()
Dim mCount, mRow As Integer

mRow = 2

Do
    Sheets("Sheet1").Range("A1:B5").Copy
    Sheets("Sheet2").Range(Cells(mRow, "A"), Cells(mRow, "B")).Insert shift:=xlDown
    mRow = mRow + 6
    mCount = mCount + 1
Loop Until mCount = 4

End Sub


Will change the data on Sheet2 into:
1 Tom
1 Cat
2 Dog
3 Mouse
4 Lion

2 Pat
1 Cat
2 Dog
3 Mouse
4 Lion

3 Mike
1 Cat
2 Dog
3 Mouse
4 Lion

4 Hank
1 Cat
2 Dog
3 Mouse
4 Lion



Best regards,
Trowa

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5792 users have said thank you to us this month

christol_1566
Posts
7
Registration date
Thursday April 11, 2019
Status
Member
Last seen
April 25, 2019
-
Hi TrowaD,
I did use your code but instead i got the reverse result in sheet 2. Here is the code

Sub RunMe()
Dim mCount, mRow As Integer

mRow = 2

Do
Sheets("Sheet1").Range("A2:C48").Copy
Sheets("Sheet2").Range(Cells(mRow, "A"), Cells(mRow, "B")).Insert shift:=xlDown
mRow = mRow + 47
mCount = mCount + 1
Loop Until mCount = 10

End Sub

My result looks like this in sheet 2:
1 Cat
2 Dog
3 Mouse
4 Lion
.
.
.
40 Lion
pasted 10 times(1-40) Then the next row had the following below
41 Tom
42 Pat
43 Mike
44 Hank

Cat, Dog, Mouse and Lion were not pasted in between Tom, Pat, Mike and Hank. The actual data that i copied had 3 columns and 47 rows that is why i made mRow= mRow + 47. Was i wrong to do this?
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
377 -
Hi Christol,

My guess would be that your Sheet2 has a header. Row 2 would then be your first row of data which is pushed down. So change "mRow = 2" into "mRow = 3", if that is the case.

Your inserted data will start at row 3 and contains 47 rows. Your second row of data of Sheet2 (before you ran the code) will now be at row 50. So you want to insert at row 51. mRow was 3 and now needs to be 51 so change "mRow = mRow +47" into "mRow = mRow +48".

The amount of copied columns doesn't matter. You can even change:
"Sheets("Sheet2").Range(Cells(mRow, "A"), Cells(mRow, "B")).Insert shift:=xlDown"
into:
"Sheets("Sheet2").Cells(mRow, "A").Insert shift:=xlDown"
As Excel adjust the inserted range to match the copied range.

Do you get the correct result now?

Best regards,
Trowa
christol_1566
Posts
7
Registration date
Thursday April 11, 2019
Status
Member
Last seen
April 25, 2019
-
Hi TrowaD, the code worked! Thanks. I am curious. what if i want to copy and paste 1000 times not 10 times, can i still use the same code? so Loop Until mCount = 1000
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
377 -
Very nice Christol and yes, you can change Loop Until mCount = 10 to 1000 if you want to copy paste a 1000 times.
Respond to TrowaD
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1565
0
Thank you
Are you familair with building for loops?

If you are, then RECORD A MACRO WITH A CUT AND PASTE OF THE DATA YOU WANT, AND LOOP IT. Then go into the macro and edit it to do what you want!

Make certian to initialize a variable for the CURRENTROW, as you will need to paste starting at a different point each time. Start with that, and we build from there!!!!


This is the easiest way to learn, because we can post some code, that you will fail to deploy, because you dont quite understand what the code does. This way! You will watch it grow, as we assist you with the changes!
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1565 -
Ok, it should be as simple as this for now:

Dim thecount
Dim theendcount
Theendcount=10

For thecount= 0 to theendcount
Thisworkbook.worksheets ("sheet2").range (yourrange).copy thisworkbook.worksheets ("sheet3").range (yourrange)

Next
christol_1566
Posts
7
Registration date
Thursday April 11, 2019
Status
Member
Last seen
April 25, 2019
-
Hi, i used your code but i got a compile error saying invalid outside procedure.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1565 -
have you defined "yourrange", or just cut and psted it? Did you try TrowaD solution?
christol_1566
Posts
7
Registration date
Thursday April 11, 2019
Status
Member
Last seen
April 25, 2019
-
Yeah I defined my range. I did try TrowaD's. It worked but it gave me a different result.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1565 -
Do you continue to get run time error? Do you know how to trap errors? Make msgbox, after making a variable, so that you can see what the variable is, and click through while developing.
Respond to ac3mark
Posts
1
Registration date
Friday April 12, 2019
Status
Member
Last seen
April 12, 2019
-1
Thank you
This is the best forum regarding Ms. Excel for both beginners as well as the experienced personals. If they follow this forum properly and regularly then they will be learning a lot regarding the software. Most of the logo design companies also maintain their data over Excel as it helps them in sorting and finding the relevant data quickly.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1565 -
I disagree, with respect. Any company that is profitable, is using a database for record keeping, Not excel!
Respond to andreajones123