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: Oldest
Most helpful Newest Oldest
  1. HansV 460.5K Reputation points MVP Volunteer Moderator
    2011-08-13T10:00:01+00:00

    You could create a macro that

    • Unprotects the sheet
    • Inserts a row
    • Copies formulas
    • Reprotects the sheet

    and make this macro available through a command button or keyboard shortcut.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-08-13T10:48:32+00:00

    Thanks Hans

    position of insert a row will calculated in one cell of sheet( for example in C10) and my protected sheet has a password which I don't like to read this password through macro by other users.

     could you please give me a macro program to cover these points?

    Regards

    Was this answer helpful?

    0 comments
  3. HansV 460.5K Reputation points MVP Volunteer Moderator
    2011-08-13T11:04:34+00:00

    What exactly do you mean by "position of insert a row will calculated in one cell of sheet"?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-08-13T11:40:32+00:00

    I need to locate in Row(...) and insert a row above it. the row # I need to insert will caculate through a formula result in C10.

    for example if C10=15 then I need to isert a row above row(15) and if C10=20 I need an insert abow row(20).

    Was this answer helpful?

    0 comments