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

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?

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

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

Very nice Christol and yes, you can change Loop Until mCount = 10 to 1000 if you want to copy paste a 1000 times.

This is what it looks like when i record the macro, i am not familiar with building loops. how do i make this code into a loop?

Sub Copy2()
'
' Copy2 Macro
'

'
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("A2:C48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-9
End Sub

A for loop is comprised as such:

Dim thecount
Dim theendcount
Theendcount =10
For thecount = 0 to theendcount
'Do you code here
Next


The above code will perform the loop eleven times.

This is how the code looks when i combine yours with the recorded macro. I keep getting an error 1004 saying select method of range failed. Is something missing in the code? thanks.

Sub Copy_3()
Dim thecount
Dim theendcount
theendcount = 10
For thecount = 0 To theendcount
Worksheets("Sheet2").Range("A2:C48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Next
End Sub

You have to specify the activeworkbook before selecting the sheet.

Btw, once this loop has completed once it will just diplicate it ten more times. You have keep track of the count and advance the rows by a multiple of the count

I disagree, with respect. Any company that is profitable, is using a database for record keeping, Not excel!