insert row in protected sheet by keeping formula

Anonymous
2011-08-13T08:15:07+00:00

Hi

I have a protected sheet contain formula in each row depends to above row( for example C2=C1+1; C3=C2+1;...).and cells contained these formula are locked.

I like to allow users to insert row everywhere by keeping formula without unprotecting the sheet. When I am clicking "insert row" while protecting the sheet, users could insert a row but the locked inserted cells are not including any formula and excel doesn't permit to copy formula of above cells. is there any solution?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 460.5K Reputation points MVP Volunteer Moderator
2011-08-14T10:16:32+00:00

If you just want the result of the formula, try

ActiveSheet.Range("E" & r).Value = ActiveSheet.Range("E1").Value

Was this answer helpful?

1 person found this answer helpful.
0 comments

42 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-10-24T20:44:51+00:00

    I have the same problem I have a protected worksheet.  I can insert a row when protected but the formula does not copy into the inserted row.(cell that are locked because they conta  in a formula). I want to be able to have users insert the row and the formula copy in the locked cells.  Can anyone help.  Thanks

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-11-04T23:16:49+00:00

    Thanks!

    This is exactly what I needed!

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2014-10-02T13:01:00+00:00

    Hello again HansV.

    I have noticed a little bit of a problem with the spreadsheet since its been in use. There are a few allowances I make for people when they work in this spreadsheet while still under protection. However when they hit the insert row button that I have created it gets rid of those exceptions and locks up the spreadsheet without the allowances/exceptions.

    Is there a way this can be fixed?

    Thanks

    Was this answer helpful?

    0 comments