Insert rows and copy formula

[costin]

Hi all,

I have the code below for inserting a certain no of rows and copy the formula. From the selected cell it inserts and copy the upper row

What I would like to do and can't by myself due to my limited knowledge about VBA:
1. To automatically run the macro only if in column A I have "yes"
2. To copy the selected row (the one where is "yes" in column A) and to insert rows below it.
3. To replace all "yes" findings in column A with "no"

Thank you all for your help!

Sub InsertRow()
    Dim Rng, n As Long, k As Long
    Application.ScreenUpdating = False
    Rng = InputBox("Enter number of rows required.")
    If Rng = "" Then Exit Sub
    Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
    k = ActiveCell.Offset(-1, 0).Row
    n = Cells(k, 256).End(xlToLeft).Column
    Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End Sub