PDA

View Full Version : [SOLVED] Copy rows and paste them to new sheet



Johnatha
02-29-2016, 12:54 PM
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
02-29-2016, 02:19 PM
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
02-29-2016, 02:36 PM
@mancubus This is perfect! Exactly what I was looking for. Thank you very much. :)

mancubus
02-29-2016, 04:02 PM
you are welcome.

thanks for the feedback and marking the thread as solved.

i added a comment for sheet that houses the table.