matthardy19
New Member
- Joined
- Aug 17, 2021
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
Hi,
I am wanting to be able to split the Materials Cost and Labour Cost to show the Total and then the grand total for each building.
I have added this formatting into the Macro already but am struggling to do the second part of defining and separating the Material & labour cost for these to calculate the individual totals.
The image attached is an example of building one of how it looks currently. Currently the Materials Total & Labour Total show the full total for the building along with the Total.
On my sheet the materials tabs names are: "ISP", "OSP", "Electrical", "Patch Leads", "Civils"& "Specials".
The Labour is called "Labour Cost".
Below is my full macro so far:
Any help to add this final part in would be greatly appreciated.
I am wanting to be able to split the Materials Cost and Labour Cost to show the Total and then the grand total for each building.
I have added this formatting into the Macro already but am struggling to do the second part of defining and separating the Material & labour cost for these to calculate the individual totals.
The image attached is an example of building one of how it looks currently. Currently the Materials Total & Labour Total show the full total for the building along with the Total.
On my sheet the materials tabs names are: "ISP", "OSP", "Electrical", "Patch Leads", "Civils"& "Specials".
The Labour is called "Labour Cost".
Below is my full macro so far:
VBA Code:
Sub Fill_Customer_Material()
Dim a, Sh, Sumbldg As Variant
Dim i, x, z, Bldg, R_Id, R_Desc, R_P, R_Q, LastRow, HeaderCnt As Long
Dim Total, BldgSubTot, Ctrl As Double
Dim CM As Worksheet
Dim MatRng As String
Dim Qty As Variant
Set CM = Sheets("Customer Materials Sheet")
HeaderCnt = 0
Sumbldg = Array(0#, 0#, 0#, 0#)
With CM.Range("AA1:AD5")
Qty = .Value
End With
For Bldg = 1 To 4
LastRow = WorksheetFunction.Max(CM.Cells(Rows.Count, 3).End(xlUp).Row, CM.Cells(Rows.Count, 7).End(xlUp).Row)
For Each Sh In Array("ISP", "OSP", "Electrical", "Patch Leads", "Civils", "Specials", "Labour Cost")
Select Case Sheets(Sh).Name
Case Is = "ISP"
MatRng = "A3:O103"
R_Id = 1
R_Desc = 3
R_P = 11
R_Q = 5 + Bldg
z = 1
Case Is = "OSP"
MatRng = "A3:P113"
R_Id = 1
R_Desc = 3
R_P = 12
R_Q = 6 + Bldg
z = 2
Case Is = "Electrical"
MatRng = "A3:O47"
R_Id = 1
R_Desc = 3
R_P = 11
R_Q = 5 + Bldg
z = 3
Case Is = "Civils"
MatRng = "A3:O101"
R_Id = 1
R_Desc = 3
R_P = 11
R_Q = 5 + Bldg
z = 4
Case Is = "Specials"
MatRng = "B3:O19"
R_Id = 1
R_Desc = 2
R_P = 8
R_Q = 2 + Bldg
z = 5
Case Is = "Labour Cost"
MatRng = "A3:O13"
R_Id = 1
R_Desc = 3
R_P = 11
R_Q = 5 + Bldg
z = 5
End Select
With Sheets(Sh).Range(MatRng)
'--------------------------------------------------------------------------------
'Check if column building qty has any values
'--------------------------------------------------------------------------------
Total = 0
Ctrl = 0
a = .Value
For i = 1 To UBound(a)
If Len(a(i, R_Q)) > 0 And IsNumeric(a(i, R_Q)) Then
If a(i, R_Q) <> 0 Then
Ctrl = Ctrl + 1
End If
End If
Next i
Qty(z, Bldg) = Ctrl
If Qty(z, Bldg) > 0 And IsNumeric(Qty(z, Bldg)) Then
'--------------------------------------------------------------------------------
'check if there's already a top header placed and if not Insert and format
'--------------------------------------------------------------------------------
If (CM.Cells(LastRow, 3).Value = "Job Description" And HeaderCnt = 0) Or (Cells(LastRow, 5).Value = "TOTAL" And HeaderCnt = 0) Then
With CM.Cells(LastRow, 3)
.Offset(3, 0).Value = "Materials List - Building: " & Bldg
.Offset(4, 0).Value = "Item Id"
.Offset(4, 1).Value = "Description"
.Offset(4, 2).Value = "Price"
.Offset(4, 3).Value = "Qty"
.Offset(4, 4).Value = "Total"
End With
With Range(CM.Cells(LastRow, 3).Offset(3, 0), CM.Cells(LastRow, 3).Offset(3, 4))
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 15773696
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
.Font.Size = 14
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.BorderAround ColorIndex:=0, Weight:=xlThin
.Merge
.HorizontalAlignment = xlCenter
End With
With Range(CM.Cells(LastRow, 3).Offset(4, 0), CM.Cells(LastRow, 3).Offset(4, 4))
.Font.Size = 12
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.BorderAround ColorIndex:=0, Weight:=xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).ColorIndex = 0
.Borders(xlInsideVertical).TintAndShade = 0
.Borders(xlInsideVertical).Weight = xlThin
.HorizontalAlignment = xlCenter
End With
HeaderCnt = 1
End If
'--------------------------------------------------------------------------------
'Insert and Format Line with Section
'--------------------------------------------------------------------------------
LastRow = WorksheetFunction.Max(CM.Cells(Rows.Count, 3).End(xlUp).Row, CM.Cells(Rows.Count, 7).End(xlUp).Row)
If Cells(LastRow, 5).Value = "Total" Then
LastRow = LastRow + 1
End If
CM.Cells(LastRow, 3).Offset(1, 0).Value = Sheets(Sh).Name
With CM.Range(Cells(LastRow, 3).Offset(1, 0), Cells(LastRow, 3).Offset(1, 4))
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorAccent5
.Interior.TintAndShade = 0.599993896298105
.Interior.PatternTintAndShade = 0
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.BorderAround ColorIndex:=0, Weight:=xlThin
End With
'--------------------------------------------------------------------------------
'Fill Material Master with lines where Qty > 0 for the building involved
'--------------------------------------------------------------------------------
For i = 1 To UBound(a)
If Len(a(i, R_Q)) > 0 And IsNumeric(a(i, R_Q)) Then
If a(i, R_Q) > 0 Then
LastRow = WorksheetFunction.Max(CM.Cells(Rows.Count, 3).End(xlUp).Row, CM.Cells(Rows.Count, 7).End(xlUp).Row)
'--------------------------------------------------------------------------------
'Next Section insert the Values
'--------------------------------------------------------------------------------
With CM.Cells(LastRow, 3)
.Offset(1, 0).Value = a(i, R_Id) 'Item ID
.Offset(1, 1).Value = a(i, R_Desc) 'Description
.Offset(1, 2).Value = Round(a(i, R_P), 2) 'price
.Offset(1, 3).Value = Round(a(i, R_Q), 2) 'Qty
.Offset(1, 4).Value = Round(a(i, R_Q) * a(i, R_P), 2) 'total
End With
'--------------------------------------------------------------------------------
'Next Section insert the formatting
'--------------------------------------------------------------------------------
CM.Cells(LastRow, 3).Offset(1, 2).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
CM.Cells(LastRow, 3).Offset(1, 4).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
With CM.Range(Cells(LastRow, 3).Offset(1, 0), Cells(LastRow, 3).Offset(1, 4))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.BorderAround ColorIndex:=0, Weight:=xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).ColorIndex = 0
.Borders(xlInsideVertical).TintAndShade = 0
.Borders(xlInsideVertical).Weight = xlThin
End With
BldgSubTot = BldgSubTot + Round(a(i, R_Q) * a(i, R_P), 2)
End If
End If
Next i
End If
End With
LastRow = WorksheetFunction.Max(CM.Cells(Rows.Count, 3).End(xlUp).Row, CM.Cells(Rows.Count, 7).End(xlUp).Row)
x = x + Qty(z, Bldg)
Next Sh
Debug.Print Sh
'--------------------------------------------------------------------------------
'Next Section insert the footer & calculation
'--------------------------------------------------------------------------------
If x > 0 Then
With CM.Cells(LastRow, 3)
.Offset(2, 2).Value = "Materials Total"
.Offset(2, 4).FormulaR1C1 = BldgSubTot
.Offset(2, 4).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
.Offset(3, 2).Value = "Labour Total"
.Offset(3, 4).FormulaR1C1 = BldgSubTot
.Offset(3, 4).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
.Offset(4, 2).Value = "Total"
.Offset(4, 4).FormulaR1C1 = BldgSubTot
.Offset(4, 4).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
End With
'--------------------------------------------------------------------------------
'Next Section applies Double Thick line Above Total
'--------------------------------------------------------------------------------
With Range(CM.Cells(LastRow, 3).Offset(5, 4), CM.Cells(LastRow, 3).Offset(5, 4))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).TintAndShade = 0
.Borders(xlEdgeTop).Weight = xlThick
End With
HeaderCnt = 0
Sumbldg(Bldg - 1) = BldgSubTot
Total = Total + BldgSubTot
BldgSubTot = 0
End If
Next Bldg
'--------------------------------------------------------------------------------
'Next Section insert closing Summary
'--------------------------------------------------------------------------------
With Range(CM.Cells(LastRow, 2).Offset(8, 0), CM.Cells(LastRow, 2).Offset(8, 6))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlMedium
End With
LastRow = LastRow + 6
With CM.Cells(LastRow, 4)
.Offset(2, 3).Value = "Total"
.Offset(3, 1).Value = "Building 1"
.Offset(4, 1).Value = "Building 2"
.Offset(5, 1).Value = "Building 3"
.Offset(6, 1).Value = "Building 4"
.Offset(7, 1).Value = "Total all Buildings"
End With
With Range(CM.Cells(LastRow, 4).Offset(2, 1), CM.Cells(LastRow, 4).Offset(2, 3))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
Range(CM.Cells(LastRow, 2).Offset(3, 0), CM.Cells(LastRow, 2).Offset(6, 0)).Font.Bold = True
For i = 1 To 4
With CM.Cells(LastRow, 4)
.Offset(2 + i, 3).Value = Sumbldg(i - 1)
.Offset(2 + i, 3).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
End With
Next i
With CM.Cells(LastRow, 4)
.Offset(7, 3).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
.Offset(7, 3).NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
End With
With Range(CM.Cells(LastRow, 2).Offset(8, 0), CM.Cells(LastRow, 2).Offset(8, 6))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).TintAndShade = 0
.Borders(xlEdgeBottom).Weight = xlMedium
End With
Any help to add this final part in would be greatly appreciated.