VBA Loop - Copy & Paste (Transpose)

[Sin_80]

Hi All,

I need to copy data from sheet 1, 1 row at a time (Fixed number of columns, but variable amount of rows) , into sheet 2, but transposed, and it loops around until it has pasted all the data from Sheet 1 1 into Sheet 2, in effect creating a big list.

My looping knowledge is sketchy and I'm drawing a blank.

Picture one is the data to copy, picture 2 is how I paste it.

Any help or support would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Sin()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) + 1), 1 To 1)
   
   For r = 2 To UBound(Ary)
      nr = nr + 1
      Nary(nr, 1) = Ary(r, 1): Nary(nr + 1, 1) = Ary(r, 2): Nary(nr + 2, 1) = Ary(r, 3)
      nr = nr + 3
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr).Value = Nary
End Sub

 

[Sin_80]

Sub Sin() Dim Ary As Variant, Nary As Variant Dim r As Long, nr As Long Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2 ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) + 1), 1 To 1) For r = 2 To UBound(Ary) nr = nr + 1 Nary(nr, 1) = Ary(r, 1): Nary(nr + 1, 1) = Ary(r, 2): Nary(nr + 2, 1) = Ary(r, 3) nr = nr + 3 Next r Sheets("Sheet2").Range("A2").Resize(nr).Value = Nary End Sub

Hi Fluff,

Thanks for the warm welcome!

Not going to pretend I understand what all the code does, but it works a charm!

Thanks for the info and the support

Sin

 

[Fluff]

You're welcome & thanks for the feedback.