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

  • Thread starter Thread starter FireBrick
  • Start date Start date
F

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.
 
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)
 
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
 
Back
Top