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

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

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.