How to copy/paste one row into multiple rows and automate it?

**tahereh1379** · 06-01-2009, 10:09 PM

Hi there,

I have an excel file with data that has 7 columns and 912 rows. I want to take the first row and copy it into 10 rows. Then take the second row and copy it into the next 10 rows, and take the third row and copy it into the next 10 rows and so on for all rows.

Please download the file here https://fileexchange.imperial.ac.uk/.../RowtoRows.xls

In the attached file, on the left hand side I have my 7 columns and 912 rows (A2:G913) . To the right of this, I have created a sample of what I am trying to achieve. I have copy&pasted A3:G3 into J3:P12, also copy pasted A4:G4 into J13:P22 etc etc (this is what I am trying to achieve but for all rows)

What formula can I use to do this quickly as it will take a long time to copy&paste manually?

Thanks in advance.

**JONvdHeyden** · 06-02-2009, 02:05 AM

Welcome to the forum.

Something like this should work. You need to add this code to a standard module in your workbook.
ALT+F11 to open the VBE
Insert > Module
Paste into the code pane on the right.
Close the VBE.

To run hit ALT+F8 and choose to run 'MyCopy' from the dialog.

Option Explicit
Sub MyCopy()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 3 To lngLastRow
    Range(Cells(lngRow, "A"), Cells(lngRow, "G")).Copy _
        Cells(Rows.Count, "J").End(xlUp).Offset(1).Resize(10, 7)
Next lngRow
End Sub

**JONvdHeyden** · 06-02-2009, 02:13 AM

A formula solution for your sample provided:

=INDEX($A$3:$G$913,(ROW()-3)/10+1,MATCH(S$2,$A$2:$G$2,0))

In J3 and copied down to end of range in column P. In this case it's P9112.

**tahereh1379** · 06-02-2009, 06:08 AM

Originally Posted by JONvdHeyden

A formula solution for your sample provided:

=INDEX($A$3:$G$913,(ROW()-3)/10+1,MATCH(S$2,$A$2:$G$2,0))

In J3 and copied down to end of range in column P. In this case it's P9112.

Hi thanks so much!

This formula didnt work for some reason