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
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?
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