Insert Row on Protected Sheet and Auto Copy Formulas

Hi All,

I have what seems like a simple problem. I have a protected sheet where column H and U are protected becuase they have formulas. I simply want to be able to insert a row e.g. between row 7 and 8, while the sheet remains protected and have the formulas auto fill to the new inserted row. At present I can insert rows but the formulas remain empty. Thanks

pw= 1

Dave

Hi Jsuarezg,

I'm only new to this, but from browsing the web others have gotten around the problem with Macro/VBA but I can't seem to find an example of the code.

I would have thought that this scenario would be a common issue amongst excel users. e.g. someone creates a spreadsheet with formulas for a user/s, and wants to protect those formulas from being accidentally over written while still allowing the user to format the sheet which includes inserting lines (easily achievable) but have formulas copy down also.

Any other suggestions?

Hello ltradey,

Please try this Test_01.xlsm

Hi rskfriends,

Thanks for the help. That's close, but I need to be able to select where the row is inserted. I found the following basic code which simply copies to row below/above. Ideally after inserting I would like the code to remove everything from the cells except for the formula cell. Any ideas?

Is there also an option to delete a row with another macro?

Sub test()
ActiveSheet.Unprotect "1"
With ActiveCell
.EntireRow.Copy
.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
ActiveSheet.Protect "1"
End Sub

hi ltradey

Try this for insert & delete.
For insert you can select cells in B column.

Formula:

Please Login or Register  to view this content.