here is something i use. it uses sheet 1 and assumes column A has a header, data
starts in A2 and you want the data in consecutive columns, 48 rows long.
watch for word wrap on the post.

Option Explicit

Sub Split_Col()
Dim ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Dim colGroup As Long
Dim remainder As Long
Set ws2 = Worksheets("Sheet1")
lastrow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
colGroup = (lastrow - 1) \ 48 ' determine number of groups
remainder = (lastrow - 1) Mod 48 ' partial group
Debug.Print colGroup & " " & remainder
z = 1
On Error Resume Next
For i = colGroup To 1 Step -1
If i 1 Then
ws2.Range("A50:A50").Resize(48).Copy
ws2.Cells(2, z + 1).PasteSpecial xlPasteAll
ws2.Rows("50").Resize(48).EntireRow.Delete
ws2.Range("A1:A1").Copy
ws2.Cells(1, z + 1).PasteSpecial xlPasteAll
ws2.Columns(z + 1).Resize(, 1).AutoFit
z = z + 1
Else
If remainder = 0 Then GoTo Xit
ws2.Range("A50:A50").Resize(remainder).Copy
ws2.Cells(2, z + 1).PasteSpecial xlPasteAll
ws2.Rows("50").Resize(remainder).EntireRow.Delete
ws2.Range("A1:A1").Copy
ws2.Cells(1, z + 1).PasteSpecial xlPasteAll
ws2.Columns(z + 1).Resize(, 1).AutoFit
End If
Next

Xit:
ws2.Range("A1").Copy
ws2.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
With ws2.Rows(1)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub



--


Gary


wrote in message
oups.com...
Hi All,

I need some help with converting my excel data from multiple rows to
multiple columns. Say i have a long list of serial numbers in a single
column, like this:

10000
10001
10002
10003
10004
10005
10006
....
19999

How can i convert it to columns of data, like this?

10000 10005 10010 .....
10001 10006 10011 ....
10002 10007 10012 ....
10003 10008 10013 ....
10004 10009 10014 ....

I need each column having a specific numbers of rows, say 40, before
the data continues to the next column.

Thanks in advance!