Prev: Pivot Tables & Conditional Formatting
Next: EQUIVALENT for this FORMULA , please
From: veggies27 on 3 Jul 2008 14:19
I am working with a worksheet that has the cells with formulas protected, to
prevent end users from modifying or overriding them (specifically column E).
My problem is I need to be able to add rows (Say 3 new rows between rows 1
and 2-which will mirror rows 1 and 2 exactly), and have the formulas in those
protected cells in the adjacent rows copied into the new rows. I can't copy
the existing row and "paste copied cells" to add the row with all the correct
formulas while the sheet is protected. I can insert a blank row, and in
theory drag and drop the 1 cell with a formula (E1) down to work in the new
row, but this is also not allowed while protected. Any suggestions?
From: Per Jessen on 3 Jul 2008 16:24
Hi

The sheet needs to be unprotcted to paste formulas!

Regards,
Per

"veggies27" <veggies27(a)discussions.microsoft.com> skrev i meddelelsen
news:FCB7FA0C-6715-4594-9972-4F9040D93222(a)microsoft.com...
>I am working with a worksheet that has the cells with formulas protected,
>to
> prevent end users from modifying or overriding them (specifically column
> E).
> My problem is I need to be able to add rows (Say 3 new rows between rows 1
> and 2-which will mirror rows 1 and 2 exactly), and have the formulas in
> those
> protected cells in the adjacent rows copied into the new rows. I can't
> copy
> the existing row and "paste copied cells" to add the row with all the
> correct
> formulas while the sheet is protected. I can insert a blank row, and in
> theory drag and drop the 1 cell with a formula (E1) down to work in the
> new
> row, but this is also not allowed while protected. Any suggestions?

From: Gord Dibben on 3 Jul 2008 16:25
Use a macro to unprotect, do your job then re-protect

Sub do_it()

ActiveSheet.Unprotect Password:="justme"

code to do your work

With ActiveSheet
.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With

End Sub


Gord Dibben MS Excel MVP

On Thu, 3 Jul 2008 11:19:02 -0700, veggies27
<veggies27(a)discussions.microsoft.com> wrote:

>I am working with a worksheet that has the cells with formulas protected, to
>prevent end users from modifying or overriding them (specifically column E).
>My problem is I need to be able to add rows (Say 3 new rows between rows 1
>and 2-which will mirror rows 1 and 2 exactly), and have the formulas in those
>protected cells in the adjacent rows copied into the new rows. I can't copy
>the existing row and "paste copied cells" to add the row with all the correct
>formulas while the sheet is protected. I can insert a blank row, and in
>theory drag and drop the 1 cell with a formula (E1) down to work in the new
>row, but this is also not allowed while protected. Any suggestions?

 | 
Pages: 1
Prev: Pivot Tables & Conditional Formatting
Next: EQUIVALENT for this FORMULA , please