I am trying to let users use a macro insert button to insert a new row in a named range (range called "Insert"). Using the code below I am able to insert a blank row and copy any formulas, but I need to be able to restrict its use to the named range only, as it will insert the new row immediately above the selected row; users do have some form of access outside the named range "Insert", and therefore, I don't wish them to be able to add a row anywhere else.
Many thanks for looking.
Regards
Paul S
Many thanks for looking.
Code:
Sub InsertRowFormulas()
ActiveSheet.Unprotect Password:="xxx"
Application.ScreenUpdating = False
Dim cell As Range
Selection.EntireRow.Insert
For Each cell In Intersect(ActiveSheet.UsedRange, Selection.Offset(-1, 0).EntireRow)
If cell.HasFormula Then
cell.Copy cell.Offset(1, 0)
End If
Next
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="xxx"
End Sub
Paul S