View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Sheeloo[_5_] Sheeloo[_5_] is offline
external usenet poster
 
Posts: 248
Default how to change data from horizontal to be vertical (Excel 2007)

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