Copy and paste transposed - help please

[Jimbeau13]

I am relatively new to this so please have patience.

I have an excel spreadsheet with about 150,000 values in column A (so A1 to A150000). I need to copy 10 of them and then paste them transposed into the 10 columns to the right (B through K).

Currently I am selecting the 10 (A1 to A10), copying them, then highlighting the next 10 cells (B1 to B10) and pasting transposed. This fills all of the next 10 columns with data.

Problem is this is very tedious and I have a long way to go. Is there something I can do to automate this process?

Thank you so much in advance.

 

[DaveBlakeMAAT]

Personally, I would load your data into PowerQuery (Get&Transform) and then transpose the data set and load it to the required location.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Jimbeau13()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To (UBound(Ary) / 10) + 1, 1 To 10)
   nr = 1
   For r = 1 To UBound(Ary)
      nc = nc + 1
      Nary(nr, nc) = Ary(r, 1)
      If nc = 10 Then nc = 0: nr = nr + 1
   Next r
   Range("B1").Resize(nr, 10).Value2 = Nary
End Sub

 

[Jimbeau13]

Thank you so much for the responses. Unfortunately I do not know where to put Fluffs code (I am new at this).

I am using Excel 2016. I will do some research and try to figure out a solution in PowerQuery. That is also a new tool to me.

Either way I really appreciate your time in this.

 

[alansidman]

Here is how to use Fluff's code



Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button