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

[FireBrick]

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.

 

[Nick Hodge]

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.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Gord Dibben]

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