The following was provided in response to my original request which was to have rows automatically added or deleted on my "target" sheet when added or removed from my "master" sheet and have the formulas automatically copy down on the "target" tab from the row above when rows are inserted.

The following works if only one row is added or deleted....can this be modified or amended to work if multiple rows are added or deleted in the "source" sheet. There are multiple tabs in the workbook but the two tabs involved here are: "Master" (Source tab) and "Target" (Tab that needs to update as the master is modified).

Like I stated, if only one row at a time is added or deleted, the code works. However, I could need to add or delete multiple rows at a time in a group....ie rows 5-8 might be deleted all at once. There could be up to 50 rows added or deleted together.


Option Explicit
Public iRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws2 As Worksheet: Set ws2 = Sheets("Target")

If Selection.Rows.Count = 1 And Selection.Columns.Count = Columns.Count Then
If iRow > ActiveSheet.UsedRange.Rows.Count Then
'deleted
ws2.Rows(Target.Row).Delete
ElseIf iRow < ActiveSheet.UsedRange.Rows.Count Then
'inserted
ws2.Rows(Target.Row).Insert
ws2.Rows(Target.Row - 1).Resize(2).FillDown
Else
MsgBox ("Uh-oh. The hamster must be on break because something went horribly wrong.")
Exit Sub
End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

iRow = ActiveSheet.UsedRange.Rows.Count

End Sub