Just copy your data

Then click paste Special

And Tick the Transpose box

Regards
Derek


"Sheeloo" just remove all As... wrote in
message ...
Sub Transpose()
Dim lastRow, lastCol As Long
Dim srcSheet As String
Dim destSheet As String
Dim i, j, k As Long
Dim RM() As String
Application.ScreenUpdating = False
srcSheet = "Sheet1"
destSheet = "Sheet2"
Worksheets(srcSheet).Activate
With Worksheets(srcSheet)
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
ReDim RM(lastCol)
For j = 2 To lastCol
RM(j) = Worksheets(srcSheet).Cells(1, j)
Next
k = 2
For i = 2 To lastRow
Sheets(destSheet).Cells(k, 1) = Worksheets(srcSheet).Cells(i, 1)
Sheets(destSheet).Cells(k, 2) = Worksheets(srcSheet).Cells(i, 2)
For j = 2 To lastCol
If (Worksheets(srcSheet).Cells(i, j) < "") Then

Sheets(destSheet).Cells(k, 3) = Worksheets(srcSheet).Cells(i, j)
k = k + 1
End If
Next j
Next i
Worksheets(destSheet).Activate
Application.ScreenUpdating = True
End Sub


"Suwat Upathambhakul (Thailand)" wrote:

I got Bill of materials in showing Raw Materials usage in horizontal
like this
RM1 RM2 RM3 RM4 RM5
FG1 5 6 3 2
FG2 2 7 1
FG3 7 5 2

but I would like to change it to be vertical as follow

FG1 RM1 5
RM2 6
RM4 3
RM5 2
FG2 RM1 2
RM3 7
RM5 1
FG3 RM2 7
RM3 5
RM4 2

could you please advise how should we do

best rgds,
suwat u.