Copying locked cells

[Tessa]

I have a ss with formula in locked columns and the whole thing protected to
stop the formula being altered. When the user comes to the last row in
which there is a formula she needs to copy this last row and paste into
however many rows she needs to complete her figures. However the protection
is not allowing the paste function to work.

I know this is probably quite basic but your forum has taught me that I know
very little about Excel.

 

[Dave Peterson]

Maybe you could give the user a macro that would copy the row above (including
any formulas).

If you want to try, start at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

You'll have to add something that unprotects the sheet near the beginning and
reprotects the sheet at the end.

 

[Tom-S]

I guess an alternative would be for the user to copy the whole sheet and
paste into a new sheet which is not (currently) protected.

Tom

 

[Dave Peterson]

I would think that this would pretty much destroy the way the workbook works
today.

I'm not sure I'd recommend this.

 

[Tom-S]

If all that was going to be done was fill down the existing formulas for as
many extra rows as data needed to be entered then I think I'd have to say
that talking of 'destroying the way the workbook works' seems be a bit
extreme. If it's a reasonably controlled environment then you should at the
very least give it a try and see whether it suits the situation.

But I'd be the first to admit that those are two big if's.

Tom

 

[Dave Peterson]

If there are other formulas/macros (in any workbook) that use/expect the data on
a certain sheet, then those would be broken.

If the developer thought putting the data on an unprotected worksheet were a
solution, then it would make more sense (to me at least) to just unprotect the
original sheet.

But maybe the OP will find it alternative that she can embrace.

 

[Tom-S]

I agree wholeheartedly that cross refs or links to other sheets or workbooks
could end up completely broken - but the original query didn't seem to me to
imply that was the case, otherwise adding new rows would have been a no go
straight from the start.

Also agree that unprotecting the original sheet may be simpler than
copy-pasting to a new workbook and letting the user develop further from
there. Nearly always more than one way to skin the proverbial cat.

Tom