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. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-05T16:38:38+00:00

    The most likely explanation is that the row above the one that is inserted by the code does not contain any formulas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-08-05T15:40:50+00:00

    Hello HansV.

    Sorry to bother you again (in fact I thought i sent this once already but it doesn't show that i did).

    I tested this at home and it worked, tried it at work on the spreadsheet i will actually use and it does not.

    It says I have to debug it and highlights this part

    Rows(CurRow - 1).SpecialCells(xlCellTypeFormulas).Resize

    It will unlock and insert a row but won't carry the formula. Any clue as to what is wrong. It calls it an error 1004, an application or object error.

    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. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-03T09:19:49+00:00

    Good to hear that. Thanks for the feedback.

    Was this answer helpful?

    0 comments