VBA Loop - Copy & Paste (Transpose)

Sin_80

New Member
Joined
Mar 24, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Data to copy.PNG
    Data to copy.PNG
    5.3 KB · Views: 8
  • Data to Paste.PNG
    Data to Paste.PNG
    2.4 KB · Views: 8

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Mar 24, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,176,267
Messages
5,902,245
Members
434,954
Latest member
kchallr00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top