how do I make a copy of a worksheet and retain formulas but not data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FireBrick - 29 Dec 2004 11:59 GMT

I have my pension tracked for 2004
Now I wish to copy all the formatting and formulas to a new worksheet for
2005
I no how to just copy, but the formatting doesn't follow and I will have to
clear all the data info.

So can I tell it to copy the 2004 worksheet to a NEW worksheet named 2005
and retain the column size, formatting, and formulas etc.

Please and thank you.

Signature

-----------------------------------------------------
"This tagline is identical to the one you are reading."
-----------------------------------------------------

Bill H. in Chicagoland

Reply to this Message

Nick Hodge - 29 Dec 2004 12:33 GMT

If you want this data in the same workbook, right click on the 2004 sheet
tab and select 'Move or Copy'. Select 'Move to end' and 'Create a copy'.

Now rename the sheet tab by double-clicking it (2005).

Now select a cell in the new 2005 sheet and select
Edit>Goto>Special...'Constants' and uncheck 'text'

This should just select hard coded numbers, no formulae etc. Now simply
press the delete key and save.

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

>I have my pension tracked for 2004
> Now I wish to copy all the formatting and formulas to a new worksheet for
[quoted text clipped - 6 lines]
>
> Please and thank you.

Reply to this Message

Gord Dibben - 29 Dec 2004 19:33 GMT

Brick

Two-pronged approach.....

Right-click on the sheet tab and "move or copy". Checkmark in "copy" and OK.

Now, on the copied sheet Edit>Go To(or just hit F5)Special>Constants.
De-select what you don't want changed and OK.

Now with constants selected, Edit>Clear Contents.

Formulas and formatting are retained.

If need be you can Edit>Replace "2004" with "2005"

Gord Dibben Excel MVP

>I have my pension tracked for 2004
>Now I wish to copy all the formatting and formulas to a new worksheet for
[quoted text clipped - 6 lines]
>
>Please and thank you.

Reply to this Message

Rate this thread: