Sub newestimate()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.CutCopyMode = False
.EnableAnimations = False
.EnableEvents = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.Iteration = True
.MaxIterations = 100
.MaxChange = 0.01
End With
ActiveSheet.DisplayPageBreaks = False
Dim worksh As Integer
Dim worksheetexists As Boolean
worksheetexists = False
worksh = Application.Sheets.Count
For x = 1 To worksh
If Worksheets(x).Name = "ESTIMATE SUMMARY" Then
worksheetexists = True
Exit For
End If
Next x
If worksheetexists = False Then
With Sheets("HIDE-EST-X")
.Visible = True
.Unprotect
End With
Sheets("ALIGNMENTS").Unprotect
Sheets("ESTIMATE TEMPLATE").Unprotect
Number = Sheets("ALIGNMENTS").Range("R15") + 1
Sheets("HIDE-EST-X").Copy after:=Sheets("ESTIMATE TEMPLATE")
ActiveSheet.Name = "ESTIMATE SUMMARY"
Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
Sheets("ESTIMATE TEMPLATE").Columns("C").Hidden = False
Sheets("ESTIMATE TEMPLATE").Range("C4:H4").Copy
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "C").PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.DisplayPageBreaks = False
Dim divName As String
i = 1
While i < Number + 1
divName = Sheets("LIST").Cells(224 + i, "Y")
If divName <> "" Then
Sheets("ESTIMATE SUMMARY").Columns("A:C").Calculate
Sheets("ESTIMATE SUMMARY").Range("B1").Calculate
Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
divRow = 224 + i
Sheets("ESTIMATE TEMPLATE").Range("C4:H304").Copy
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow + 1, "C").PasteSpecial Paste:=xlPasteFormats
.Range(Cells(Lastrow + 1, "C"), Cells(Lastrow + 2, "H")).Value = Sheets("ESTIMATE TEMPLATE").Range("C4:H5").Value
.Range(Cells(Lastrow + 3, "D"), Cells(Lastrow + 300, "D")).FormulaArray = "=TRANSPOSE(LIST!$Z$4:$LK$4)"
.Range(Cells(Lastrow + 3, "E"), Cells(Lastrow + 300, "E")).FormulaArray = "=TRANSPOSE(LIST!$Z$6:$LK$6)"
.Range(Cells(Lastrow + 3, "F"), Cells(Lastrow + 300, "F")).FormulaArray = "=TRANSPOSE(LIST!$Z$" & divRow & ":$LK$" & divRow & ")"
.Cells(Lastrow + 3, "G").Formula = "='ESTIMATE TEMPLATE'!$G6"
.Range(Cells(Lastrow + 3, "G"), Cells(Lastrow + 300, "G")).FillDown
.Range(Cells(Lastrow + 3, "H"), Cells(Lastrow + 300, "H")).FormulaR1C1 = "=RC[-2]*RC[-1]"
.Cells(Lastrow + 301, "D") = divName & " Total:"
.Cells(Lastrow + 301, "H").Formula = "=SUM(H" & Lastrow + 3 & ":H" & Lastrow + 300 & ")"
With .Cells(Lastrow + 301, "C").Borders(xlEdgeLeft)
.LineStyle = Continuous
.Weight = xlThick
End With
End With
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow + 302, "B"), Cells(Lastrow + 305, "B")) = 1
.Cells(Lastrow, "C") = divName & " Estimate:"
.Cells(Lastrow, "C").HorizontalAlignment = xlLeft
.Range(Cells(Lastrow, "C"), Cells(Lastrow, "D")).Locked = False
.Range(Cells(Lastrow, "C"), Cells(Lastrow, "D")).Interior.Color = RGB(221, 235, 247)
End With
With Sheets("ESTIMATE SUMMARY").Cells(Lastrow, "C").Font
.Bold = True
.Size = 12
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3, "G"), Cells(Lastrow + 300, "G"))
.EntireRow.AutoFit
.Interior.ColorIndex = 0
.Locked = True
End With
Dim party1 As Integer
Dim party2 As Integer
Dim party3 As Integer
Dim parties As Integer
party1 = 0
party2 = 0
party3 = 0
If Sheets("ESTIMATE TEMPLATE").Range("M1") <> vbNullString Then
party1 = 1
End If
If Sheets("ESTIMATE TEMPLATE").Range("M2") <> vbNullString Then
party2 = 1
End If
If Sheets("ESTIMATE TEMPLATE").Range("O1") <> vbNullString Then
party3 = 1
End If
parties = party1 + party2 + party3
If parties = 2 Then
Sheets("ESTIMATE TEMPLATE").Range("L4:M304").Copy _
Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "I")
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 2, "I"), Cells(Lastrow + 301, "I"))
.Interior.Color = RGB(252, 228, 214)
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J"))
.Interior.Color = RGB(237, 237, 237)
End With
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 3, "I").Formula = "='ESTIMATE TEMPLATE'!$L6"
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "I")).FillDown
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 3, "J").Formula = "='ESTIMATE TEMPLATE'!$M6"
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3, "J"), Cells(Lastrow + 300, "J")).FillDown
Sheets("ESTIMATE TEMPLATE").Range("O4:R304").Copy _
Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "K")
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow + 2, "I") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Cells(Lastrow + 1, "K") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Range(Cells(Lastrow + 3, "K"), Cells(Lastrow + 300, "K")).FormulaR1C1 = "=RC[-2] * RC[-5]"
.Range(Cells(Lastrow + 3, "L"), Cells(Lastrow + 300, "L")).FormulaR1C1 = "=RC[-1] * RC[-5]"
.Cells(Lastrow + 301, "L").Formula = "=SUM($L$" & Lastrow + 3 & ":$L$" & Lastrow + 300 & ")"
.Cells(Lastrow + 299, "U") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Cells(Lastrow + 299, "V").Formula = "=$L$" & Lastrow + 301
.Cells(Lastrow + 2, "J") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Cells(Lastrow + 1, "M") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Range(Cells(Lastrow + 3, "M"), Cells(Lastrow + 300, "M")).FormulaR1C1 = "=RC[-3] * RC[-7]"
.Range(Cells(Lastrow + 3, "N"), Cells(Lastrow + 300, "N")).FormulaR1C1 = "=RC[-1] * RC[-7]"
.Cells(Lastrow + 301, "N").Formula = "=SUM($N$" & Lastrow + 3 & ":$N$" & Lastrow + 300 & ")"
.Cells(Lastrow + 300, "U") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Cells(Lastrow + 300, "V").Formula = "=$N$" & Lastrow + 301
End With
Sheets("ESTIMATE TEMPLATE").Range("U4:U304").Copy _
Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "O")
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow + 3, "O"), Cells(Lastrow + 300, "O")).FormulaR1C1 = "=RC[-3] + RC[-1]"
.Cells(Lastrow + 301, "O").Formula = "=SUM($O$" & Lastrow + 3 & ":$O$" & Lastrow + 300 & ")"
End With
With Sheets("ESTIMATE SUMMARY")
.Columns("I:J").ColumnWidth = 12
.Columns("K").ColumnWidth = 12
.Columns("M").ColumnWidth = 12
.Columns("L").ColumnWidth = 16
.Columns("N").ColumnWidth = 16
.Columns("O").ColumnWidth = 16
With .Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "J"))
.Locked = True
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlRight).LineStyle = Continuous
.Borders(xlRight).Weight = xlMedium
.FormatConditions.delete
End With
With .Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J"))
With .Borders(xlEdgeRight)
.LineStyle = Continuous
.Weight = xlMedium
End With
With .Borders(xlEdgeLeft)
.LineStyle = Continuous
.Weight = xlThin
End With
End With
End With
End If
If parties = 3 Then
Sheets("ESTIMATE TEMPLATE").Range("L4:U304").Copy _
Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "I")
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow + 2, "I"), Cells(Lastrow + 301, "I")).Interior.Color = RGB(252, 228, 214)
.Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J")).Interior.Color = RGB(237, 237, 237)
.Range(Cells(Lastrow + 2, "K"), Cells(Lastrow + 301, "K")).Interior.Color = RGB(255, 242, 204)
.Cells(Lastrow + 3, "I").Formula = "='ESTIMATE TEMPLATE'!$L6"
.Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "I")).FillDown
.Cells(Lastrow + 3, "J").Formula = "='ESTIMATE TEMPLATE'!$M6"
.Range(Cells(Lastrow + 3, "J"), Cells(Lastrow + 300, "J")).FillDown
.Cells(Lastrow + 3, "K").Formula = "='ESTIMATE TEMPLATE'!$N6"
.Range(Cells(Lastrow + 3, "K"), Cells(Lastrow + 300, "K")).FillDown
End With
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow + 2, "I") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Cells(Lastrow + 1, "L") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Range(Cells(Lastrow + 3, "L"), Cells(Lastrow + 300, "L")).FormulaR1C1 = "=RC[-3] * RC[-6]"
.Range(Cells(Lastrow + 3, "M"), Cells(Lastrow + 300, "M")).FormulaR1C1 = "=RC[-1] * RC[-6]"
.Cells(Lastrow + 301, "M").Formula = "=SUM($M$" & Lastrow + 3 & ":$M$" & Lastrow + 300 & ")"
.Cells(Lastrow + 299, "U") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Cells(Lastrow + 299, "V").Formula = "=$M$" & Lastrow + 301
.Cells(Lastrow + 2, "J") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Cells(Lastrow + 1, "N") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Range(Cells(Lastrow + 3, "N"), Cells(Lastrow + 300, "N")).FormulaR1C1 = "=RC[-4] * RC[-8]"
.Range(Cells(Lastrow + 3, "O"), Cells(Lastrow + 300, "O")).FormulaR1C1 = "=RC[-1] * RC[-8]"
.Cells(Lastrow + 301, "O").Formula = "=SUM($O$" & Lastrow + 3 & ":$O$" & Lastrow + 300 & ")"
.Cells(Lastrow + 300, "U") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Cells(Lastrow + 300, "V").Formula = "=$O$" & Lastrow + 301
.Cells(Lastrow + 2, "K") = Sheets("ESTIMATE TEMPLATE").Range("O1")
.Cells(Lastrow + 1, "P") = Sheets("ESTIMATE TEMPLATE").Range("O1")
.Range(Cells(Lastrow + 3, "P"), Cells(Lastrow + 300, "P")).FormulaR1C1 = "=RC[-5] * RC[-10]"
.Range(Cells(Lastrow + 3, "Q"), Cells(Lastrow + 300, "Q")).FormulaR1C1 = "=RC[-1] * RC[-10]"
.Cells(Lastrow + 301, "Q").Formula = "=SUM($Q$" & Lastrow + 3 & ":$Q$" & Lastrow + 300 & ")"
.Cells(Lastrow + 301, "U") = Sheets("ESTIMATE TEMPLATE").Range("O1")
.Cells(Lastrow + 301, "V").Formula = "=$Q$" & Lastrow + 301
End With
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow + 3, "R"), Cells(Lastrow + 300, "R")).FormulaR1C1 = "=RC[-5] + RC[-3] + RC[-1]"
.Cells(Lastrow + 301, "R").Formula = "=SUM($R$" & Lastrow + 3 & ":$R$" & Lastrow + 300 & ")"
End With
With Sheets("ESTIMATE SUMMARY")
.Columns("I:K").ColumnWidth = 12
.Columns("L").ColumnWidth = 12
.Columns("N").ColumnWidth = 12
.Columns("P").ColumnWidth = 12
.Columns("M").ColumnWidth = 16
.Columns("O").ColumnWidth = 16
.Columns("Q:R").ColumnWidth = 16
With .Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "K"))
.Locked = True
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlRight).LineStyle = Continuous
.Borders(xlRight).Weight = xlMedium
.FormatConditions.delete
End With
With .Range(Cells(Lastrow + 2, "K"), Cells(Lastrow + 301, "K"))
With .Borders(xlEdgeRight)
.LineStyle = Continuous
.Weight = xlMedium
End With
With .Borders(xlEdgeLeft)
.LineStyle = Continuous
.Weight = xlThin
End With
End With
End With
End If
itemno = 1
For k = Lastrow + 3 To Lastrow + 300
If Cells(k, "F") > 0 Then
Cells(k, "C") = itemno
itemno = itemno + 1
Else
Rows(k).Hidden = True
End If
Next k
If InStr(divName, "Base") > 0 Then
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "S") = "Base"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "T").FormulaR1C1 = "=RC[-12]"
End If
divno = 1
While divno < Number + 1
If InStr(divName, "Div. " & divno) > 0 Then
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "S") = "Div. " & divno
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "T").FormulaR1C1 = "=RC[-12]"
End If
divno = divno + 1
Wend
End If
i = i + 1
Wend
Sheets("ESTIMATE SUMMARY").Columns("A:B").Calculate
Sheets("ESTIMATE SUMMARY").Range("B1").Calculate
Dim num As Integer
num = Sheets("ALIGNMENTS").Range("U15") + 1
Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
Dim minuscol As Integer
minuscol = Lastrow - 15
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "D") = "DIVISION CONSTRUCTION"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "F") = "TOTAL"
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1, "D"), Cells(Lastrow + 1, "E"))
.Merge
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1, "F"), Cells(Lastrow + 1, "G"))
.Merge
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1, "D"), Cells(Lastrow + 1, "G"))
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Borders(xlEdgeTop).LineStyle = Continuous
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlEdgeLeft).LineStyle = Continuous
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeRight).LineStyle = Continuous
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeBottom).LineStyle = Continuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlInsideVertical).LineStyle = Continuous
.Borders(xlInsideVertical).Weight = xlThin
End With
Dim counter As Integer
counter = 0
For n = 1 To num
If Sheets("ALIGNMENTS").Cells(15 + n, "U") <> 0 Then
counter = counter + 1
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1 + counter, "D") = Sheets("ALIGNMENTS").Cells(15 + n, "T")
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1 + counter, "F").Formula = "=SUMIF($S$15:$S$" & Lastrow + 1 & ",D" & Lastrow + 1 + counter & ",$T$15:$T$" & Lastrow + 1 & ")"
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1 + counter, "D"), Cells(Lastrow + 1 + counter, "E")).Merge
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1 + counter, "F"), Cells(Lastrow + 1 + counter, "G")).Merge
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1 + counter, "D"), Cells(Lastrow + 1 + counter, "G"))
.Borders(xlEdgeLeft).LineStyle = Continuous
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeRight).LineStyle = Continuous
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeBottom).LineStyle = Continuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = Continuous
.Borders(xlInsideVertical).Weight = xlThin
End With
End If
Next n
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow + 2 + counter, "D") = "TOTAL PROJECT CONSTRUCTION:"
.Cells(Lastrow + 2 + counter, "F").Formula = "=SUM(F" & Lastrow + 2 & ":F" & Lastrow + 1 + counter & ")"
.Range(Cells(Lastrow + 2 + counter, "D"), Cells(Lastrow + 2 + counter, "E")).Merge
.Range(Cells(Lastrow + 2 + counter, "F"), Cells(Lastrow + 2 + counter, "G")).Merge
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 2 + counter, "D"), Cells(Lastrow + 2 + counter, "G"))
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = Continuous
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeRight).LineStyle = Continuous
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeBottom).LineStyle = Continuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlInsideVertical).LineStyle = Continuous
.Borders(xlInsideVertical).Weight = xlThin
End With
With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 2, "F"), Cells(Lastrow + 2 + counter, "F"))
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End With
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3 + counter, "B"), Cells(Lastrow + 6 + counter, "B")) = 1
Sheets("ESTIMATE SUMMARY").Columns("A:B").Calculate
Sheets("ESTIMATE SUMMARY").Range("B1").Calculate
Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
With Sheets("PROJECT SHEET")
.Unprotect
End With
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1, "D"), Cells(Lastrow + 21, "G")).Formula = "=if(isblank('PROJECT SHEET'!B45)=false,'PROJECT SHEET'!B45,"""")"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 5, "F").FormulaR1C1 = "=RC[-1] * R[6]C"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 6, "F").FormulaR1C1 = "=RC[-1] * R[5]C"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 7, "F").FormulaR1C1 = "=RC[-1] * R[4]C"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 8, "F").FormulaR1C1 = "=RC[-1] * R[3]C"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 9, "F").FormulaR1C1 = "=RC[-1] * R[2]C"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 10, "F").FormulaR1C1 = "=RC[-1] * R[1]C"
Sheets("PROJECT SHEET").Range("B45:E66").Copy
Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 1, "D"), Cells(Lastrow + 21, "G")).PasteSpecial xlPasteFormats
Sheets("PROJECT SHEET").Range("B66:E66").Copy _
Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 22, "D")
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "G").Formula = "=$F$" & Lastrow - 4
Sheets("ESTIMATE SUMMARY").Rows(Lastrow + 19).Hidden = True
Sheets("ESTIMATE SUMMARY").Rows(Lastrow + 20).Hidden = True
If parties > 1 Then
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow, "H") = Sheets("ESTIMATE TEMPLATE").Range("M1")
.Cells(Lastrow, "I") = Sheets("ESTIMATE TEMPLATE").Range("M2")
.Cells(Lastrow + 1, "H").Formula = "=SUMIF($U$14:$U$" & Lastrow - 2 & ",$H$" & Lastrow & ",$V$14:$V$" & Lastrow - 2 & ")"
.Cells(Lastrow + 1, "I").Formula = "=SUMIF($U$14:$U$" & Lastrow - 2 & ",$I$" & Lastrow & ",$V$14:$V$" & Lastrow - 2 & ")"
.Cells(Lastrow + 2, "H").Formula = "=$E$" & Lastrow + 2 & "*$H$" & Lastrow + 1
.Cells(Lastrow + 2, "I").Formula = "=$E$" & Lastrow + 2 & "*$I$" & Lastrow + 1
.Cells(Lastrow + 11, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$311 * $F$" & Lastrow + 11 & ")"
.Cells(Lastrow + 11, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$311 * $F$" & Lastrow + 11 & ")"
.Cells(Lastrow + 12, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$312 * $F$" & Lastrow + 12 & ")"
.Cells(Lastrow + 12, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$312 * $F$" & Lastrow + 12 & ")"
.Cells(Lastrow + 13, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$313 * $F$" & Lastrow + 13 & ")"
.Cells(Lastrow + 13, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$313 * $F$" & Lastrow + 13 & ")"
.Cells(Lastrow + 14, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$314 * $F$" & Lastrow + 14 & ")"
.Cells(Lastrow + 14, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$314 * $F$" & Lastrow + 14 & ")"
.Cells(Lastrow + 15, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$315 * $F$" & Lastrow + 15 & ")"
.Cells(Lastrow + 15, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$315 * $F$" & Lastrow + 15 & ")"
.Cells(Lastrow + 16, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$316 * $F$" & Lastrow + 16 & ")"
.Cells(Lastrow + 16, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$316 * $F$" & Lastrow + 16 & ")"
.Cells(Lastrow + 17, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$317 * $F$" & Lastrow + 17 & ")"
.Cells(Lastrow + 17, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$317 * $F$" & Lastrow + 17 & ")"
.Cells(Lastrow + 18, "H").Formula = "=('ESTIMATE TEMPLATE'!$P$318 * $F$" & Lastrow + 18 & ")"
.Cells(Lastrow + 18, "I").Formula = "=('ESTIMATE TEMPLATE'!$Q$318 * $F$" & Lastrow + 18 & ")"
.Cells(Lastrow + 21, "H").Formula = "=SUM($H$" & Lastrow + 11 & ":$H$" & Lastrow + 20 & ")"
.Cells(Lastrow + 21, "I").Formula = "=SUM($I$" & Lastrow + 11 & ":$I$" & Lastrow + 20 & ")"
.Cells(Lastrow + 22, "H").Formula = "=$H$" & Lastrow + 1 & "+$H$" & Lastrow + 2 & "+$H$" & Lastrow + 21
.Cells(Lastrow + 22, "I").Formula = "=$I$" & Lastrow + 1 & "+$I$" & Lastrow + 2 & "+$I$" & Lastrow + 21
With .Range(Cells(Lastrow, "H"), Cells(Lastrow + 22, "H"))
.Interior.Color = RGB(252, 228, 214)
End With
With .Range(Cells(Lastrow, "I"), Cells(Lastrow + 22, "I"))
.Interior.Color = RGB(237, 237, 237)
End With
End With
End If
If parties = 3 Then
With Sheets("ESTIMATE SUMMARY")
.Cells(Lastrow, "J") = Sheets("ESTIMATE TEMPLATE").Range("O1")
.Cells(Lastrow + 1, "J").Formula = "=SUMIF($U$14:$U$" & Lastrow - 2 & ",$J$" & Lastrow & ",$V$14:$V$" & Lastrow - 2 & ")"
.Cells(Lastrow + 2, "J").Formula = "=$E$" & Lastrow + 2 & "*$J$" & Lastrow + 1
.Cells(Lastrow + 11, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$311 * $F$" & Lastrow + 11 & ")"
.Cells(Lastrow + 12, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$312 * $F$" & Lastrow + 12 & ")"
.Cells(Lastrow + 13, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$313 * $F$" & Lastrow + 13 & ")"
.Cells(Lastrow + 14, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$314 * $F$" & Lastrow + 14 & ")"
.Cells(Lastrow + 15, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$315 * $F$" & Lastrow + 15 & ")"
.Cells(Lastrow + 16, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$316 * $F$" & Lastrow + 16 & ")"
.Cells(Lastrow + 17, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$317 * $F$" & Lastrow + 17 & ")"
.Cells(Lastrow + 18, "J").Formula = "=('ESTIMATE TEMPLATE'!$R$318 * $F$" & Lastrow + 18 & ")"
.Cells(Lastrow + 21, "J").Formula = "=SUM($J$" & Lastrow + 11 & ":$J$" & Lastrow + 20 & ")"
.Cells(Lastrow + 22, "J").Formula = "=$J$" & Lastrow + 1 & "+$J$" & Lastrow + 2 & "+$J$" & Lastrow + 21
With .Range(Cells(Lastrow, "J"), Cells(Lastrow + 22, "J"))
.Interior.Color = RGB(255, 242, 204)
End With
End With
End If
If parties = 2 Then
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow, "H"), Cells(Lastrow, "I")).Font.Bold = True
With .Range(Cells(Lastrow, "H"), Cells(Lastrow + 22, "I"))
.Borders(xlInsideVertical).LineStyle = Continuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = Continuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlInsideHorizontal).LineStyle = Continuous
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = Continuous
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeTop).LineStyle = Continuous
.Borders(xlEdgeTop).Weight = xlThick
.HorizontalAlignment = xlCenter
End With
With .Range(Cells(Lastrow + 1, "H"), Cells(Lastrow + 22, "I"))
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End With
With .Cells(Lastrow, "H").Borders(xlEdgeLeft)
.LineStyle = Continuous
.Weight = xlThick
End With
With .Range(Cells(Lastrow + 21, "H"), Cells(Lastrow + 21, "I"))
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
With .Range(Cells(Lastrow + 22, "H"), Cells(Lastrow + 22, "I"))
.Font.Bold = True
End With
With .Range(Cells(Lastrow + 1, "H"), Cells(Lastrow + 1, "I")).Borders(xlEdgeBottom)
.LineStyle = Continuous
.Weight = xlMedium
End With
With .Range(Cells(Lastrow + 2, "H"), Cells(Lastrow + 2, "I"))
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
End With
End If
If parties = 3 Then
With Sheets("ESTIMATE SUMMARY")
.Range(Cells(Lastrow, "H"), Cells(Lastrow, "J")).Font.Bold = True
With .Range(Cells(Lastrow, "H"), Cells(Lastrow + 22, "J"))
.Borders(xlInsideVertical).LineStyle = Continuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = Continuous
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlInsideHorizontal).LineStyle = Continuous
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = Continuous
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeTop).LineStyle = Continuous
.Borders(xlEdgeTop).Weight = xlThick
.HorizontalAlignment = xlCenter
End With
With .Range(Cells(Lastrow + 1, "H"), Cells(Lastrow + 22, "J"))
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End With
With .Cells(Lastrow, "H").Borders(xlEdgeLeft)
.LineStyle = Continuous
.Weight = xlThick
End With
With .Range(Cells(Lastrow + 21, "H"), Cells(Lastrow + 21, "J"))
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
With .Range(Cells(Lastrow + 1, "H"), Cells(Lastrow + 1, "J")).Borders(xlEdgeTop)
.LineStyle = Continuous
.Weight = xlMedium
End With
With .Range(Cells(Lastrow + 22, "H"), Cells(Lastrow + 22, "J"))
.Font.Bold = True
End With
With .Range(Cells(Lastrow + 2, "H"), Cells(Lastrow + 2, "J"))
.Borders(xlEdgeTop).LineStyle = xlDouble
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
End With
End If
Dim nonfirstrow As Integer
nonfirstrow = Lastrow + 1
Sheets("ESTIMATE SUMMARY").Columns("A:B").Calculate
Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
With Sheets("ESTIMATE SUMMARY").Range(Cells(nonfirstrow, "E"), Cells(Lastrow - 1, "G"))
.Interior.ColorIndex = 0
.Locked = True
End With
Sheets("PROJECT SHEET").Protect
Sheets("ESTIMATE SUMMARY").Columns("C").Hidden = False
Dim countworksheets As Integer
Dim feecalcws As Boolean
feecalcws = False
countworksheets = Application.Sheets.Count
For c = 1 To countworksheets
If Worksheets(c).Name = "FEE CALCULATIONS" Then
feecalcws = True
Exit For
End If
Next c
If feecalcws = False Then
With Sheets("FEE CALC TEMPLATE")
.Visible = True
.Unprotect
End With
Sheets("FEE CALC TEMPLATE").Copy after:=Sheets("ESTIMATE SUMMARY")
ActiveSheet.Name = "FEE CALCULATIONS"
Sheets("FEE CALCULATIONS").Range("A12").Formula = "='ESTIMATE SUMMARY'!$G$" & Lastrow + 1
LineLength = Sheets("LIST").Range("N157")
Sheets("FEE CALCULATIONS").Range("N22") = LineLength
MaxSize = Sheets("LIST").Range("I2")
If MaxSize < 6 Then
Sheets("FEE CALCULATIONS").Range("N23") = 6
Else
Sheets("FEE CALCULATIONS").Range("N23") = MaxSize
End If
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 11, "F").Formula = "='FEE CALCULATIONS'!$D$11"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 12, "F").Formula = "='FEE CALCULATIONS'!$E$11"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 13, "F").Formula = "='FEE CALCULATIONS'!$I$97"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 14, "F").Formula = "='FEE CALCULATIONS'!$I$100"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 15, "F").Formula = "='FEE CALCULATIONS'!$I$31"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 16, "F").Formula = "='FEE CALCULATIONS'!$I$64"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 17, "F").Formula = "='FEE CALCULATIONS'!$I$88"
Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 18, "F").Formula = "='FEE CALCULATIONS'!$I$11"
With Sheets("FEE CALC TEMPLATE")
.Protect
.Visible = False
End With
Else
Sheets("FEE CALCULATIONS").Select
MsgBox ("Sheets Named ""ESTIMATE SUMMARY"" and ""FEE CALCULATIONS"" must be deleted or renamed to run this macro. Delete or rename these sheets and try again.")
End If
With Sheets("HIDE-EST-X")
.Protect
.Visible = False
End With
Sheets("ESTIMATE TEMPLATE").Columns("C").Hidden = False
Sheets("ESTIMATE TEMPLATE").Protect
Sheets("ALIGNMENTS").Protect
Sheets("ESTIMATE SUMMARY").Columns("E:F").AutoFit
Sheets("ESTIMATE SUMMARY").Columns("G:H").ColumnWidth = 16
Sheets("ESTIMATE SUMMARY").Protect
Sheets("ESTIMATE SUMMARY").Select
Else
Sheets("ESTIMATE SUMMARY").Select
MsgBox ("Sheets Named ""ESTIMATE SUMMARY"" and ""FEE CALCULATIONS"" must be deleted or renamed to run this macro. Delete or rename these sheets and try again.")
End If
With Application
.EnableEvents = True
.EnableAnimations = True
.DisplayAlerts = True
.CutCopyMode = True
.DisplayStatusBar = True
.Calculation = xlCalculationAutomatic
.Iteration = True
.MaxIterations = 100
.MaxChange = 0.01
.ScreenUpdating = True
End With
If feecalcws = False Then
If worksheetexists = False Then
With Sheets("FEE CALCULATIONS")
.Range("H11").Formula = "=D11+E11+I31+I64+I88+I101"
End With
Sheets("FEE CALCULATIONS").Protect
End If
End If
End Sub