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
    2016-09-12T14:03:29+00:00

    Good day Hans V

    I read through this thread & would like to find out if you could perhaps e-mail me this sample workbook?

    The dropbox link does not work any longer :-)

    I've got a similar scenario than cgbs6183 

    Thank you in advance!

    Dewald van Rooyen

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-10-24T22:52:42+00:00

    Thanks for you help I think it worked.  To delete a row I used this macro but it is giving me a debug error can you help also please?

    Sub deleterow()

    ' Delete a row '

    Selection.EntireRow.Delete Shift:=xlUp

    End Sub

    I got it, I used your code using delete Row and Fillup instead of insert and fill down

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-10-24T22:03:56+00:00

    Thanks for you help I think it worked.  To delete a row I used this macro but it is giving me a debug error can you help also please?

    Sub deleterow()

    ' Delete a row '

    Selection.EntireRow.Delete Shift:=xlUp

    End Sub

    Was this answer helpful?

    0 comments
  4. HansV 460.5K Reputation points MVP Volunteer Moderator
    2015-10-24T20:55:27+00:00

    Have you looked at the macros posted in this thread? They show how to unprotect the sheet, insert a row, copy formulas from the row above, and then protect the sheet again.

    Was this answer helpful?

    0 comments