Macro That Inserts a New Row and Defines Formulas to Cells

[mmore073]

I have created this Macro that inserts a new raw and preserves all of the conditional formatting, however, when I try to define formulas to specific cells it stops working. Please let me know where I'm going wrong.

Private Sub CommandButton1_Click()
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Interior.ColorIndex = 0
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Font.Bold = False
Sheets("Main").Range("A7:B7,D7:G7").HorizontalAlignment = xlLeft
Sheets("Main").Range("H7").HorizontalAlignment = xlRight
Sheets("Main").Range("A8:AW8").Select
Selection.Copy
Sheets("Main").Range("A7:AW7").Select
Selection.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
Application.CutCopyMode = False
Sheets("Main").Range("A7:AW7").ClearContents
Sheets("Main").Range("p7").Select
ActiveCell.Formula = "=IFS(AND($Q7<>"",$R7<>""),"Proposal Sent",AND(Q7="",R7<>""),"Error",AND($Q7="",$R7=""),"", _
AND($Q7>=TODAY()),SUM(TODAY()-$Q7),AND($Q7<TODAY()),SUM(TODAY()-$Q7))"
Sheets("Main").Range("b7").Select
ActiveCell.Formula = "=p7"
End Sub

Thanks.

[arnelgp]

you "double" all your Quotes:

ActiveCell.Formula = "=IFS(AND($Q7<>"""",$R7<>""""),""Proposal Sent"", _
AND(Q7="""",R7<>""""),""Error"",AND($Q7="""",$R7=""""),"""", _
AND($Q7>=TODAY()),SUM(TODAY()-$Q7), _
AND($Q7<TODAY()),SUM(TODAY()-$Q7))"

[Bob Phillips]

And do get rid of that horrible selecting

Private Sub CommandButton1_Click()

    With Sheets("Main")
    
        With .Range("A7")
        
            .EntireRow.Insert Shift:=xlDown
            .EntireRow.Interior.ColorIndex = 0
            .EntireRow.Font.Bold = False
        End With
        .Range("A7:B7,D7:G7").HorizontalAlignment = xlLeft
        .Range("H7").HorizontalAlignment = xlRight
        .Range("A8:AW8").Copy
        .Range("A7:AW7").PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
        .Range("A7:AW7").ClearContents ""
        .Range("P7").Formula = "=IFS(AND($Q7<>"",$R7<>""""),""Proposal Sent""," & _
                                    "AND(Q7="""",R7<>""""),""Error""," & _
                                    "AND($Q7="""",$R7=""""),""""," & _
                                    "AND($Q7>=TODAY()),SUM(TODAY()-$Q7)," & _
                                    "AND($Q7<TODAY()),SUM(TODAY()-$Q7))"
        .Range("B7").Formula = "=P7"
    End With
End Sub