Insert a row and copy formula in a named range

[paulstan]

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.

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

Regards

Paul S

 

[NARAYANK991]

Hi ,

Can you give more details ? Is it possible you can upload your workbook ?

Or can you answer the following question ?

Suppose the range named Insert has the reference C7:J17 ; now suppose the user places the cursor anywhere outside this range , say for instance in cell B7 and runs this macro ; what should happen ?

Suppose the cursor is in C7 i.e. the first row of the named range , and the macro is run ; what should happen ?

Suppose the cursor is in J18 i.e. immediately after the last row of the named range , and the macro is run ; what should happen ?

Narayan

 

[paulstan]

Hi Narayan

Many thanks for your comments. I have uploaded a very simplistic workbook of what I am trying to achieve.

The named range (in the example) is from A6:N15. The worksheet will be protected, so I will allow the users to insert a row via a macro and then protect the worksheet after they have done. Now, the only place I want them to insert a row and copy the formula is when they place their cusor anywhere in the named range, preferrably a new row above the selected row. From the example workbook, users are also allowed to enter data in areas other than the named range (everything highlighted in green will be areas that users can enter data). If they are allowed to insert a row at, say A2, then this compromises the spreadsheet integrity, with the possibility of everything collapsing. So, if someone selects a cell that is outside the named range and runs the macro, I would like a pop-up error message box to be shown informing them that this operation is not allowed. I don't want to use a table, because a new row may need to be inserted anywhere in the named range and not just at the bottom of the table.

Many thanks

Paul S