howiechiang
New Member
- Joined
- Jan 27, 2014
- Messages
- 12
I am compiling a list of BOMs. It will first list the assembly name, and then list all of it's components in the rows underneath.
The assemblies name and fields pulled with it must be bold. The list of components underneath it must be group together, so i can collapse all the groups and just see the assemblies.
In my code, the "For Each oAssembly In oAssemblies" it pulls assembly p/n, quantity, and description. The "For Each oCell In oQuantity" nested in that loop pulls the information of individual piece parts in that assembly.
Please help.
The assemblies name and fields pulled with it must be bold. The list of components underneath it must be group together, so i can collapse all the groups and just see the assemblies.
In my code, the "For Each oAssembly In oAssemblies" it pulls assembly p/n, quantity, and description. The "For Each oCell In oQuantity" nested in that loop pulls the information of individual piece parts in that assembly.
Please help.
Code:
Public Sub SortBOM()
Dim oAssembliesRow As Range
Dim oAssemblies As Range
Dim oBOM As Range
Dim oPieceParts As Range
Dim oQuantity As Range
Dim oCell As Range
Dim oAssembly As Range
'Delete current BOM sheet and create a new'
For Each ws In ThisWorkbook.Sheets
If ws.Name = "BOM" Then
ws.Delete
End If
Next
Sheets.Add.Name = "BOM"
'Select cells to check for quantity & location to store that information'
Set oAssembliesRow = ThisWorkbook.Worksheets("Parts Count").Range("D4:CC4")
Set oBOM = Worksheets("BOM").Range("A1")
Set oAssemblies = oAssembliesRow.SpecialCells(xlCellTypeConstants)
'Input the title of fields in oBOM sheet'
oBOM.Value = "Assemblies"
oBOM.Offset(0, 1).Value = "Piece Part"
oBOM.Offset(0, 2).Value = "Revision"
oBOM.Offset(0, 3).Value = "Quantity"
oBOM.Offset(0, 4).Value = "Description"
'Create'
For Each oAssembly In oAssemblies
Worksheets("Parts Count").Activate
Set oPieceParts = Range(Cells(7, oAssemblies.Column), Cells(Rows.Count, oAssemblies.Column))
Set oQuantity = oPieceParts.SpecialCells(xlCellTypeConstants)
oPieceParts.SpecialCells(xlCellTypeConstants).Activate
oBOM.Offset(1, 0).Value = Worksheets("Parts Count").Cells(6, oAssemblies.Column).Value
oBOM.Offset(1, 3).Value = Worksheets("Parts Count").Cells(4, oAssemblies.Column).Value
oBOM.Offset(1, 4).Value = Worksheets("Parts Count").Cells(5, oAssemblies.Column).Value
Set oBOM = oBOM.Offset(1, 0)
Worksheets("BOM").Activate
For Each oCell In oQuantity
Set oBOM = oBOM.Offset(1, 0)
oBOM.Offset(0, 1).Value = Worksheets("Parts Count").Cells(oCell.Row, 2).Value
oBOM.Offset(0, 2).Value = Worksheets("Parts Count").Cells(oCell.Row, 3).Value
oBOM.Offset(0, 3).Value = Worksheets("Parts Count").Cells(oCell.Row, oQuantity.Column).Value
oBOM.Offset(0, 4).Value = Worksheets("Parts Count").Cells(oCell.Row, 1).Value
Next oCell
Next oAssembly
End Sub
