Macro-assigned button causes error

K

khanna

New Member
Joined
Jan 15, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a code that generates two new sheets based off of templates every time that it runs. I have a button that the macro is assigned to. When I click the button, it generates the first of the two sheets and then gives me an error 400 message. I go back, delete the sheet that was generated, and click the button a second time, and it does not give an error message.

When I run the code from the editor (stop/play button), it works perfectly. I have also gone through the code line by line with the F8 key and can’t find an issue with the code.

I’ve tried reassigning the macro to the button as well as completely deleting the button and creating a new one, but nothing that I can think of is working. Any ideas on what could be causing this?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
khanna said:
I have a code that generates two new sheets based off of templates every time that it runs. I have a button that the macro is assigned to. When I click the button, it generates the first of the two sheets and then gives me an error 400 message. I go back, delete the sheet that was generated, and click the button a second time, and it does not give an error message.

When I run the code from the editor (stop/play button), it works perfectly. I have also gone through the code line by line with the F8 key and can’t find an issue with the code.

I’ve tried reassigning the macro to the button as well as completely deleting the button and creating a new one, but nothing that I can think of is working. Any ideas on what could be causing this?

Here is the code. I know there's a lot of places where I could have done things more efficiently. I'm still learning, so it is pretty long and messy.
Click to expand...
VBA Code: 
Sub newestimate()
    'ESTIMATE SUMMARY FOR USDA
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
    
' COPYING AND RENAMING SHEET AND SETTING COLUMN WIDTHS
    
    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 ' added this so that it doesn't do anything with the row between the linework div./alt. and the MISC. div. names
        
        ' CALCULATING SECTION:
        Sheets("ESTIMATE SUMMARY").Columns("A:C").Calculate
        Sheets("ESTIMATE SUMMARY").Range("B1").Calculate
        Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")
        divRow = 224 + i
    
        ' copy formatting and column widths from template into ESTIMATE SUMMARY
            Sheets("ESTIMATE TEMPLATE").Range("C4:H304").Copy ' itemrange
          
            
        With Sheets("ESTIMATE SUMMARY")
            .Cells(Lastrow + 1, "C").PasteSpecial Paste:=xlPasteFormats
            
        ' copy title rows from template into ESTIMATE SUMMARY
            .Range(Cells(Lastrow + 1, "C"), Cells(Lastrow + 2, "H")).Value = Sheets("ESTIMATE TEMPLATE").Range("C4:H5").Value
        
        'copy description formula arrays into ESTIMATE summary
            .Range(Cells(Lastrow + 3, "D"), Cells(Lastrow + 300, "D")).FormulaArray = "=TRANSPOSE(LIST!$Z$4:$LK$4)" ' itemrange
        'Copy unit formula arrays into ESTIMATE SUMMARY
            .Range(Cells(Lastrow + 3, "E"), Cells(Lastrow + 300, "E")).FormulaArray = "=TRANSPOSE(LIST!$Z$6:$LK$6)" ' itemrange
        ' copy est. quanity formula arrays into ESTIMATE SUMMARY
            .Range(Cells(Lastrow + 3, "F"), Cells(Lastrow + 300, "F")).FormulaArray = "=TRANSPOSE(LIST!$Z$" & divRow & ":$LK$" & divRow & ")" ' itemrange
        
        ' copy est. unit price from template into ESTIMATE SUMMARY
            .Cells(Lastrow + 3, "G").Formula = "='ESTIMATE TEMPLATE'!$G6"
            .Range(Cells(Lastrow + 3, "G"), Cells(Lastrow + 300, "G")).FillDown ' itemrange
        ' copy formula for est. sub-total from template into ESTIMATE SUMMARY
            .Range(Cells(Lastrow + 3, "H"), Cells(Lastrow + 300, "H")).FormulaR1C1 = "=RC[-2]*RC[-1]" ' itemrange
        
        ' copy TOTAL line at bottom from template into ESTIMATE SUMMARY
            .Cells(Lastrow + 301, "D") = divName & " Total:" ' itemrange
            .Cells(Lastrow + 301, "H").Formula = "=SUM(H" & Lastrow + 3 & ":H" & Lastrow + 300 & ")" ' itemrange
            With .Cells(Lastrow + 301, "C").Borders(xlEdgeLeft)
                .LineStyle = Continuous
                .Weight = xlThick
            End With
        End With

        With Sheets("ESTIMATE SUMMARY")
        ' adding lines after to have space before next div/alt
            .Range(Cells(Lastrow + 302, "B"), Cells(Lastrow + 305, "B")) = 1 ' itemrange
        ' formatting
            .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")) ' itemrange
                .EntireRow.AutoFit
                .Interior.ColorIndex = 0
                .Locked = True
            End With
            
' START PAYER COLUMNS
    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("O4:R264").Copy _
        '    Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "I") ' itemrange
            'percentages
                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)
                        '.Locked = True
                    End With
                    With Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J"))
                        .Interior.Color = RGB(237, 237, 237)
                        '.Locked = True
                    End With
                    ' making percents formulas so that if you change them in the ESTIMATE TEMPLATE sheet they'll change in all of the div. too:
                        Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 3, "I").Formula = "='ESTIMATE TEMPLATE'!$L6"
                            Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "I")).FillDown ' itemrange
                        Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 3, "J").Formula = "='ESTIMATE TEMPLATE'!$M6"
                            Sheets("ESTIMATE SUMMARY").Range(Cells(Lastrow + 3, "J"), Cells(Lastrow + 300, "J")).FillDown ' itemrange
  
            'parties:
                Sheets("ESTIMATE TEMPLATE").Range("O4:R304").Copy _
                    Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "K") ' itemrange (ABOVE)
                With Sheets("ESTIMATE SUMMARY")
                'party 1:
                    .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]" ' itemrange
                    .Range(Cells(Lastrow + 3, "L"), Cells(Lastrow + 300, "L")).FormulaR1C1 = "=RC[-1] * RC[-5]" ' itemrange
                    .Cells(Lastrow + 301, "L").Formula = "=SUM($L$" & Lastrow + 3 & ":$L$" & Lastrow + 300 & ")" ' itemrange (2)
                        'START VLOOKUP
                            .Cells(Lastrow + 299, "U") = Sheets("ESTIMATE TEMPLATE").Range("M1") ' itemrange
                            .Cells(Lastrow + 299, "V").Formula = "=$L$" & Lastrow + 301 ' itemrange 2
                        'END VLOOKUP
                    'party 2:
                    .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]" ' itemrange
                    .Range(Cells(Lastrow + 3, "N"), Cells(Lastrow + 300, "N")).FormulaR1C1 = "=RC[-1] * RC[-7]" ' itemrange
                    .Cells(Lastrow + 301, "N").Formula = "=SUM($N$" & Lastrow + 3 & ":$N$" & Lastrow + 300 & ")" ' itemrange 2
                        'START VLOOKUP
                            .Cells(Lastrow + 300, "U") = Sheets("ESTIMATE TEMPLATE").Range("M2") ' itemrange
                            .Cells(Lastrow + 300, "V").Formula = "=$N$" & Lastrow + 301 ' itemrange 2
                        'END VLOOKUP
                End With
            'total:
               Sheets("ESTIMATE TEMPLATE").Range("U4:U304").Copy _
                Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "O") ' itemrange (ABOVE)
                With Sheets("ESTIMATE SUMMARY")
                    .Range(Cells(Lastrow + 3, "O"), Cells(Lastrow + 300, "O")).FormulaR1C1 = "=RC[-3] + RC[-1]" ' itemrange
                    .Cells(Lastrow + 301, "O").Formula = "=SUM($O$" & Lastrow + 3 & ":$O$" & Lastrow + 300 & ")" ' itemrange 2
                End With
            'formatting:
                With Sheets("ESTIMATE SUMMARY")
                    'column widths
                        .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
                    'PERCENTAGES
                        With .Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "J")) ' itemrange
                            .Locked = True
                            .Borders(xlInsideHorizontal).Weight = xlThin
                            .Borders(xlRight).LineStyle = Continuous
                            .Borders(xlRight).Weight = xlMedium
                            .FormatConditions.delete 'new
                        End With
                        With .Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J")) ' itemrange
                            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") ' itemrange (ABOVE)
            'percentages
                With Sheets("ESTIMATE SUMMARY")
                    .Range(Cells(Lastrow + 2, "I"), Cells(Lastrow + 301, "I")).Interior.Color = RGB(252, 228, 214) ' itemrange
                    .Range(Cells(Lastrow + 2, "J"), Cells(Lastrow + 301, "J")).Interior.Color = RGB(237, 237, 237) ' itemrange
                    .Range(Cells(Lastrow + 2, "K"), Cells(Lastrow + 301, "K")).Interior.Color = RGB(255, 242, 204) ' itemrange
                    'formulas
                    .Cells(Lastrow + 3, "I").Formula = "='ESTIMATE TEMPLATE'!$L6"
                        .Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "I")).FillDown ' itemrange
                    .Cells(Lastrow + 3, "J").Formula = "='ESTIMATE TEMPLATE'!$M6"
                        .Range(Cells(Lastrow + 3, "J"), Cells(Lastrow + 300, "J")).FillDown ' itemrange
                    .Cells(Lastrow + 3, "K").Formula = "='ESTIMATE TEMPLATE'!$N6"
                        .Range(Cells(Lastrow + 3, "K"), Cells(Lastrow + 300, "K")).FillDown ' itemrange
                End With
  
            'parties:
                With Sheets("ESTIMATE SUMMARY")
                'party 1:
                    .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]" ' itemrange
                    .Range(Cells(Lastrow + 3, "M"), Cells(Lastrow + 300, "M")).FormulaR1C1 = "=RC[-1] * RC[-6]" ' itemrange
                    .Cells(Lastrow + 301, "M").Formula = "=SUM($M$" & Lastrow + 3 & ":$M$" & Lastrow + 300 & ")" ' itemrange 2
                        'START VLOOKUP
                            .Cells(Lastrow + 299, "U") = Sheets("ESTIMATE TEMPLATE").Range("M1") ' itemrange
                            .Cells(Lastrow + 299, "V").Formula = "=$M$" & Lastrow + 301 ' itemrange 2
                        'END VLOOKUP
                'party 2:
                    .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]" ' itemrange
                    .Range(Cells(Lastrow + 3, "O"), Cells(Lastrow + 300, "O")).FormulaR1C1 = "=RC[-1] * RC[-8]" ' itemrange
                    .Cells(Lastrow + 301, "O").Formula = "=SUM($O$" & Lastrow + 3 & ":$O$" & Lastrow + 300 & ")" ' itemrange 2
                        'START VLOOKUP
                            .Cells(Lastrow + 300, "U") = Sheets("ESTIMATE TEMPLATE").Range("M2") ' itemrange
                            .Cells(Lastrow + 300, "V").Formula = "=$O$" & Lastrow + 301 ' itemrange 2
                        'END VLOOKUP
                'party 3:
                    .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]" ' itemrange
                    .Range(Cells(Lastrow + 3, "Q"), Cells(Lastrow + 300, "Q")).FormulaR1C1 = "=RC[-1] * RC[-10]" ' itemrange
                    .Cells(Lastrow + 301, "Q").Formula = "=SUM($Q$" & Lastrow + 3 & ":$Q$" & Lastrow + 300 & ")" ' itemrange 2
                        'START VLOOKUP
                            .Cells(Lastrow + 301, "U") = Sheets("ESTIMATE TEMPLATE").Range("O1") ' itemrange
                            .Cells(Lastrow + 301, "V").Formula = "=$Q$" & Lastrow + 301 ' itemrange 2
                        'END VLOOKUP
                End With
            'total:
                With Sheets("ESTIMATE SUMMARY")
                    .Range(Cells(Lastrow + 3, "R"), Cells(Lastrow + 300, "R")).FormulaR1C1 = "=RC[-5] + RC[-3] + RC[-1]" ' itemrange
                    .Cells(Lastrow + 301, "R").Formula = "=SUM($R$" & Lastrow + 3 & ":$R$" & Lastrow + 300 & ")" ' itemrange 2
                End With
            'formatting:
                With Sheets("ESTIMATE SUMMARY")
                    'column widths
                        .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
                    'PERCENTAGES
                        With .Range(Cells(Lastrow + 3, "I"), Cells(Lastrow + 300, "K")) ' itemrange
                            .Locked = True
                            .Borders(xlInsideHorizontal).Weight = xlThin
                            .Borders(xlRight).LineStyle = Continuous
                            .Borders(xlRight).Weight = xlMedium
                            .FormatConditions.delete 'new
                        End With
                        With .Range(Cells(Lastrow + 2, "K"), Cells(Lastrow + 301, "K")) ' itemrange
                            With .Borders(xlEdgeRight)
                                .LineStyle = Continuous
                                .Weight = xlMedium
                            End With
                            With .Borders(xlEdgeLeft)
                                .LineStyle = Continuous
                                .Weight = xlThin
                            End With
                        End With
                End With

    End If


'END PAYER COLUMNS

        ' Assigning item numbers:
            itemno = 1
            For k = Lastrow + 3 To Lastrow + 300 ' itemrange
                If Cells(k, "F") > 0 Then
                    Cells(k, "C") = itemno
                    itemno = itemno + 1
                Else
                    Rows(k).Hidden = True
                End If
            Next k
            
        ' Helper columns (S and T) for whole division totals and counting number of sections:
            If InStr(divName, "Base") > 0 Then
                Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "S") = "Base" ' itemrange
                Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "T").FormulaR1C1 = "=RC[-12]" ' itemrange
            End If
                divno = 1
                While divno < Number + 1
                    If InStr(divName, "Div. " & divno) > 0 Then
                        Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "S") = "Div. " & divno ' itemrange
                        Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 301, "T").FormulaR1C1 = "=RC[-12]" ' itemrange
                    End If
                divno = divno + 1
                Wend
            End If ' ending if to ignore the row between line div./alt. and MISC. div. names
    i = i + 1
Wend

Sheets("ESTIMATE SUMMARY").Columns("A:B").Calculate
Sheets("ESTIMATE SUMMARY").Range("B1").Calculate

' DIVISION TOTALS
    Dim num As Integer
    num = Sheets("ALIGNMENTS").Range("U15") + 1
    Lastrow = Sheets("ESTIMATE SUMMARY").Range("B1")

' DIVISION TOTALS TABLE
    Dim minuscol As Integer
    minuscol = Lastrow - 15
    
    ' TABLE TITLE:
    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").FormulaR1C1 = "=SUM(VLOOKUP(RC[-2], R[-" & minuscol & "]C[13]:R[-1]C[14], 2))"
            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
        
    ' TOTAL PROJECT CONSTRUCTION LINE
        With Sheets("ESTIMATE SUMMARY")
            .Cells(Lastrow + 2 + counter, "D") = "TOTAL PROJECT CONSTRUCTION:"
        'Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 3 + counter, "E").Formula = "=SUM(E" & Lastrow + 3 & ":E" & Lastrow + 2 + counter & ")"
            .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
' END CODE FOR DIVISION TOTALS TABLE

' START CODE FOR THE NONCONSTRUCTION HERE - HAVE TO UNPROTECT THIS SHEET
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") 'CHANGE REF
    .Unprotect
End With

'Sheets("PROJECT SHEET").Range("B45:E66").Copy _
    Destination:=Worksheets("ESTIMATE SUMMARY").Cells(Lastrow + 1, "D") 'CHANGE REF
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").Cells(Lastrow + 3, "G").Formula = "=$F$" & Lastrow - 7
Sheets("ESTIMATE SUMMARY").Rows(Lastrow + 19).Hidden = True
Sheets("ESTIMATE SUMMARY").Rows(Lastrow + 20).Hidden = True

    'START PARTY PERCENTAGES ON NONCONSTRUCTION TABLE
        If parties > 1 Then
            With Sheets("ESTIMATE SUMMARY")
                .Cells(Lastrow, "H") = Sheets("ESTIMATE TEMPLATE").Range("M1")
                .Cells(Lastrow, "I") = Sheets("ESTIMATE TEMPLATE").Range("M2")
                    'START VLOOKUP
                        .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 & ")"
                    'END VLOOKUP
                
                    'START CONTINGENCY
                        .Cells(Lastrow + 2, "H").Formula = "=$E$" & Lastrow + 2 & "*$H$" & Lastrow + 1
                        .Cells(Lastrow + 2, "I").Formula = "=$E$" & Lastrow + 2 & "*$I$" & Lastrow + 1
                    'END CONTINGENCY
                    
                .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")
                    'START VLOOKUP
                        .Cells(Lastrow + 1, "J").Formula = "=SUMIF($U$14:$U$" & Lastrow - 2 & ",$J$" & Lastrow & ",$V$14:$V$" & Lastrow - 2 & ")"
                    'END VLOOKUP
                    
                    'START CONTINGENCY
                        .Cells(Lastrow + 2, "J").Formula = "=$E$" & Lastrow + 2 & "*$J$" & Lastrow + 1
                    'END CONTINGENCY
                    
                .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
                '.Font.Bold = True
            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
                '.Font.Bold = True
            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
        
    'END PARTY PERCENTAGES ON NONCONSTRUCTION TABLE





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
' END CODE FOR NONCONSTRUCTION TABLE

Sheets("ESTIMATE SUMMARY").Columns("C").Hidden = False
    
'START NEW FEE CALCULATIONS SHEET
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 'FEE CALCULATIONS sheet - Construction Amount
LineLength = Sheets("LIST").Range("N157") 'getting line length from LIST sheet by copying and pasting value -not linking
Sheets("FEE CALCULATIONS").Range("N22") = LineLength 'copying line length into FEE CALCULATIONS sheet
MaxSize = Sheets("LIST").Range("I2") 'copying line max size without linking
If MaxSize < 6 Then
    Sheets("FEE CALCULATIONS").Range("N23") = 6
    Else
    Sheets("FEE CALCULATIONS").Range("N23") = MaxSize 'putting max line size into FEE CALCULATIONS sheet
End If
    'START ESTIMATE SUMMARY SHEET
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 11, "F").Formula = "='FEE CALCULATIONS'!$D$11" 'Engineering Basic Services (Engineering Fee)
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 12, "F").Formula = "='FEE CALCULATIONS'!$E$11" 'Inspection Services
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 13, "F").Formula = "='FEE CALCULATIONS'!$I$97" 'Legal (Loan Closing)
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 14, "F").Formula = "='FEE CALCULATIONS'!$I$100" 'Legal (Property Acquisition)
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 15, "F").Formula = "='FEE CALCULATIONS'!$I$31" 'Environmental Report
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 16, "F").Formula = "='FEE CALCULATIONS'!$I$64" 'Additional Engineering Services
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 17, "F").Formula = "='FEE CALCULATIONS'!$I$88" 'Permitting Fees, Advertising, Etc.
    Sheets("ESTIMATE SUMMARY").Cells(Lastrow + 18, "F").Formula = "='FEE CALCULATIONS'!$I$11" 'Interim Interest
    'END ESTIMATE SUMMARY SHEET
    



    'protecting and rehiding FEE CALC TEMPLATE sheet at end:
    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 'ending if for if sheet named "FEE CALCULATIONS" EXISTS



'END NEW FEE CALCULATIONS SHEET
    
    
    
    
    
    
    
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
 
Upvote 0
Reading the code, it looks OK (nothing obvious anyway). I'll carry on looking in case something comes to mind ... without actually having the book, and seeing the error in action, it is going to be a bit difficult to debug. I hope others are looking at this too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Assigning Macro to a button not working but works when run manually
Replies
2
Views
818
Skyybot
Skyybot
T
  • Question Question
Macros disabled from spreadsheet copy
Replies
2
Views
143
travellerva
T
J
  • Question Question
Macro to assign to print button with specific criteria
2
Replies
11
Views
582
Micron
M
J
  • Question Question
I added my macro's to the quick access toolbar but the buttons disappear when I run the code
Replies
8
Views
337
Jurg55
J
D
  • Solved
Show worksheets independently using macro assigned buttons
2 3
Replies
22
Views
302
dmt32
D

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top