Macro to add and delete lines

[Constellation8]

Dear Excel Helpers,

I'm creating a template which has links and formulas from one (source)worksheet to other worksheets within a workbook.

For this template, I am trying to create a macro that adds a line above the same row for each worksheet. I can do this for a specified row (ie. row 44). Can I do this: when the user of the template highlights a row from the source worksheet and inserts a row, can the VBA macro then find the same numbered row in other worksheets and add a line? Thus, all the worksheets will have the same rows.

For deleting a row, I found how to select a row or cell and delete the entire row (below):
If TypeName(Selection) = "Range" Then
Selection.EntireRow.Delete
End If

I then want to automatically delete the same rows (which will have #REF! across the row) in the other worksheets. Any ideas on this?

My goal is for a user to update the source worksheet and the other worksheets with the formulas will be updated automatically.

Thanks for any suggestions you provide.

 

[iggydarsa]

maybe you can do something like this; instead if deleting the row from the template manually, just run the macro below... this macro will delete the selected row, and the same row number from all the worksheets:

Sub Macro1()
    Dim r As Long: r = Selection.Row
    Dim c As Long: c = Selection.Column
    
    For Each sh In Worksheets
        sh.Cells(r, c).EntireRow.Delete
    Next sh
    
End Sub

 

[Constellation8]

Thank you so much, Iggydarsa.

For an extra challenge, do you think there's a way to highlight more than one row and delete all the rows on each sheet?