Copy rows and paste them to new sheet

[Johnatha]

Hello!

I'm looking for a macro that will copy rows 1&2 of Sheet1, then paste (transpose) those rows on to a new sheet (Sheet2). Then, copy rows 1&3 and transpose to a new sheet (Sheet3). Then, copy rows 1&4 and transpose to a new sheet (Sheet4), and so on... Is this possible? I have 200 rows in total, and would hate to do this manually.

Candidate	Q1	Q2	Q3	Q4	Q5
Jim	y	z	1	2	3
Bill	x	x	x	x	x
Steve	u	u	u	u	u
Teeg	1	2	2	2	1
Jip	4	g	g	g	g

Thanks!!

[mancubus]

try:

Sub vbax_55295_copy_header_and_rows_accross_new_sheets()

    Dim tblArr
    Dim i As Long
    
    tblArr = Worksheets("Sheet1").Cells(1).CurrentRegion.Value 'Change Sheet1 to suit
    
    For i = 2 To UBound(tblArr)
        With Worksheets.Add(After:=Worksheets(Worksheets.Count))
            .Range("A1").Resize(UBound(tblArr, 2)).Value = Application.Transpose(Application.Index(tblArr, 1, 0))
            .Range("B1").Resize(UBound(tblArr, 2)).Value = Application.Transpose(Application.Index(tblArr, i, 0))
        End With
    Next i

End Sub

http://snb-vba.eu/VBA_Arrays_en.html

[Johnatha]

@mancubus This is perfect! Exactly what I was looking for. Thank you very much.

[mancubus]

you are welcome.

thanks for the feedback and marking the thread as solved.

i added a comment for sheet that houses the table.