Prev: Default Cell format of existing Worksheets got changed automatical
Next: Toolbar is gone
From: Tessa on 21 May 2010 08:54
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.
From: Dave Peterson on 21 May 2010 10:38
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.

Tessa wrote:
>
> 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
From: Tom-S on 21 May 2010 12:38
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" wrote:

> 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.
>
> Tessa wrote:
> >
> > 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
> .
>
From: Dave Peterson on 21 May 2010 14:25
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 wrote:
>
> 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" wrote:
>
> > 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.
> >
> > Tessa wrote:
> > >
> > > 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
> > .
> >

--

Dave Peterson
From: Tom-S on 21 May 2010 17:21
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" wrote:

> 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 wrote:
> >
> > 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" wrote:
> >
> > > 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.
> > >
> > > Tessa wrote:
> > > >
> > > > 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
> > > .
> > >
>
> --
>
> Dave Peterson
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Default Cell format of existing Worksheets got changed automatical
Next: Toolbar is gone