View Single Post
Old 05-20-2018, 09:05 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 814
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough

Based on your original post and attached file.
You only need to use column B data as your source range.
Sub TransposeBom()
    Dim lr As Long, rng As Range, cel As Range
    Dim ChildNum As Integer
    Dim destSheet As Worksheet, writeRow As Long
With Sheets("Source or Input")
    lr = .Range("B" & Rows.Count).End(xlUp).Row
    Set rng = .Range("B2:B" & lr)
End With

Set destSheet = Sheets("Desired Output")
writeRow = 5   'to keep away from posted desired results

For Each cel In rng
    If cel.Value <> cel.Offset(-1).Value Then
        ChildNum = 1
        ChildNum = ChildNum + 1
    End If
    With destSheet
        If ChildNum = 1 Then
            writeRow = writeRow + 1
            .Cells(writeRow, 1).Resize(, 3).Value = cel.Resize(, 3).Value
            .Cells(writeRow, 4).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
            .Cells(writeRow, 4).Offset(, (ChildNum - 1) * 5).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
        End If
    End With
Next cel
End Sub
Attached Files
File Type: xlsm 1-BomTranspose Macro - Forum.xlsm (19.7 KB, 3 views)
Reply With Quote