How to carry over functions/Formulas into new rows while using the command button macro to add new rows?- Please Help:)

[JSoto74]

Hello all,

I'm new here and just starting to dive into the more complicated world of excel

I created a command button via VBA to add a new row below to the top of my list for entry but now having issues with the functions/formulas not copying over. For example a simple =(??) to mirror another cell in the new row or a function such as =IF(COUNTA(Z6:AA6)=2,"Y","N") to carry over as well. See below for what works so far for adding the new line just need some help with figuring out the rest. Please help!


Private Sub CommandButton1_Click()

Sheets("2019-2020 R&R Tracker Test").Range("A7").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("2019-2020 R&R Tracker Test").Range("A7:AK7").Select
Selection.Borders.Weight = xlThin

End Sub

 

[JSoto74]

Still having an issue. Seem the last macro is in red due to having this input incorrectly. See below of what I have so far...please help!

Private Sub CommandButton1_Click()

Sheets("Food & Beverage").Range("A7").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Food & Beverage").Range("A7:AJ7").Select
Selection.Borders.Weight = xlThin

Sheets("Food & Beverage").Range("r7").Select
ActiveCell.Formula = "=(e7)"

Sheets("Food & Beverage").Range("s7").Select
ActiveCell.Formula = "=(f7)"

Sheets("Food & Beverage").Range("s7").Select
ActiveCell.Formula = "IF(COUNTA(Z6:AA6)=2,"Yes","No")"

 

[Fluff]

You need to double-up on the quotes inside the formula, like

"=IF(COUNTA(Z6:AA6)=2,""Yes"",""No"")"

You are also missing the = sign.

 

[Fluff]

You can also clean up that code like

With Sheets("Food & Beverage")
   .Range("A7").EntireRow.Insert Shift:=xlDown
   .Range("A7:AJ7").Borders.Weight = xlThin
   .Range("r7").Formula = "=(e7)"
   .Range("s7").Formula = "=(f7)"
   .Range("s7").Formula = "=IF(COUNTA(Z6:AA6)=2,""Yes"",""No"")"
End With