Dear Experts,
With respect to my previous post I have made a Macro as follows which is working fine:-
Sub TransposeBom()
Option Explicit
Dim SrcRng As Range 'Source Range
Dim TrgRng As Range 'Target Range
Dim i As Integer, SrcNoofRecords As Integer, r As Integer
Dim RowOffset As Integer, Columnoffset As Integer
Dim PrevParentCode As String, CurrParentCode As String
Dim PrevParentCellAdd As Range
Dim CurrParentCellAdd As Range
Dim PrevRow, PrevCol As Integer
RowOffset = 0
Columnoffset = 0
c = 0
i = 1
j = 0
With ActiveSheet
Set SrcRng = Application.InputBox("Select a Source Range from Where Datat is to be Copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & SrcRng.Address)
Set TrgRng = Application.InputBox("Select a Cell in which the data is to be copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & TrgRng.Address)
End With
r1 = SrcRng(1).Row - 1
c1 = SrcRng(1).Column - 1
PrevParentCode = SrcRng(1).Value
R2 = r1 + 1
MsgBox (" The rows " & SrcRng.Rows.Count & " and the Columns " & SrcRng.Columns.Count)
For Each CELL In SrcRng.Cells
TrgRng.Cells.Offset(RowOffset, Columnoffset).Value = CELL.Value
c = CELL.Column
If c = 9 Then
If SrcRng.Cells(r1, 1) = SrcRng.Cells(R2, 1) Then
Columnoffset = Columnoffset + 1
r1 = r1 + 1
R2 = R2 + 1
'
Else
Columnoffset = 0
RowOffset = RowOffset + 1
r1 = r1 + 1
R2 = R2 + 1
End If
Else
Columnoffset = Columnoffset + 1
End If
Next CELL
End Sub
My source data was as per attached image:-
I am getting the output as shown in attached image by executing the above Macro:-
the only addition in above program needed is avoiding the repetition of Parent Items highlighted in red in above image. Any sort of help in above regard will be great help to me. Thanks in advance.
best regards