Insert new Row when a cell has a value within cells range

Question

I need a macro to copy and insert a row below an existing when a cell had a value within a cell range, below macro insert a blank rows without coping cell value,

to be more specific, I need to when I fill in a cell within a range copy and insert a certain row below of it with this new cell value

Sub new-macro()
    Dim rng As Range
    Dim cell As Range

    Set rng = Range("F2:F12")

    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            ActiveCell.Offset(1, 0).Select
            ActiveCell.EntireRow.Insert
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Next
End Sub

---

I found this script but it gives me "subscript out of range", any idea why plz?

Sub insertRowFormatFromAbove() 

Worksheets("Insert row").Rows(27).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove 

End Sub

Answer 1

To add (copy and insert) a specific row when you change a value in the range F2:F12 you must use the Worksheet_Change event.

You probably must adjust Rows(1).Copy to the row number you want to copy.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2:F12")) Is Nothing Then 'if changed cell is within the range
        If Not IsEmpty(Target) Then
            Rows(1).Copy 'copy row number 1
            Application.EnableEvents = False 'prevent the following line to trigger the change event again
            Target.Offset(RowOffset:=1).EntireRow.Insert Shift:=xlShiftDown 'insert copied row
            Application.EnableEvents = True 're enable events
        End If
    End If
End Sub

• I recommend you to read How to avoid using Select in Excel VBA. Using Select is a bad practice and makes your code slow, so it is highly recommended to avoid it.