row to columns

[Lavan]

Hello,

I need a macro to convert row to columns. My data like below

Forest:A
Tree:A
Tree:B
Tree:C

Forest:B
Tree:A
Tree:B
Tree:C
Tree:D

Forest:z1

Forest:1
Tree:1
Tree:2
Tree:5
Tree:10
Tree:15

Forest:X
Tree:A
Tree:1
Tree:Z
Tree:33

Forest:20
Tree:A

I want this data in the spread to be converted into columns like below.

Forest Tree Tree Tree Tree Tree
A A B C
B A B C D
z1
1 1 2 5 10 15
X A 1 Z 33
20 A


Any help is greatly appriciated. Thanks in advance.

Lavan

 

[Dylan]

Code below will transpose data in active sheet to Sheet2.

Sub Test()
colNum = 1
line1:

If Not IsEmpty(Cells(1, colNum)) Then
    lastRow = Cells(Rows.Count, colNum).End(xlUp).Row
    For I = 1 To lastRow
        Cells(I, colNum).Copy Destination:=Sheets("Sheet2").Cells(colNum, I)
    Next I
    colNum = colNum + 1
    GoTo line1
End If

End Sub