Here is the macro for the benefit of other readers...
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 = "BOM (Bill of materials)"
destSheet = ActiveSheet.Name
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 = 3 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 = 3 To lastCol
If (Worksheets(srcSheet).Cells(i, j) < "") Then
Sheets(destSheet).Cells(k, 3) = RM(j)
Sheets(destSheet).Cells(k, 4) = Worksheets(srcSheet).Cells(i, j)
k = k + 1
End If
Next j
Next i
Worksheets(destSheet).Activate
Application.ScreenUpdating = True
End Sub
Sub clearCells()
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Range(Cells(2, 1), Cells(lastRow, lastCol)).clear
End Sub
"Suwat Upathambhakul (Thailand)" wrote:
Dear Sheeloo
your advise is helpful, I sent my file to wikiend.com as per your advice,
thanks again,
rgds, suwat u.
"Sheeloo" wrote:
NO.
I upload to a site like wikisend.com and paste the link...
You can mail it to me...
"Suwat Upathambhakul (Thailand)" wrote:
Dear Sheeloo
thank you so much, but we have many many finished goods, do you know how to
attach file into this forum ?
thank you so much,
"Sheeloo" wrote:
Copy the data you want...
Select a cell outside the copied area
Right click, choose Paste Special and select Transpose...
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.
"Suwat Upathambhakul (Thailand)" wrote:
Bill of materials for many Finished goods showing Raw materials in
horizontal, I would like to change Raw Materials from horizontal to vertical.
Kindly advised how to do that.
thanks and best regards,
suwat upathambhakul