COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

[HANKYPANK]

How can I copy a workbook that has formulas without copying the info in the cells
I want to retain the formulas in the cells of the new workbook, but want to delete the data in the cells, so I have a blank workbook, but still have the formulas for the cells.

 

[Bernie Deitrick]

HankyP,

Select all cells (click the box above and to the left of cell A1) then
use Edit | Go To.... Special..., Constants, click OK, then hit
Delete. Note that this will also get rid of headers and labels, so if
you need to keep those, narrow done your initial selection to just
cells that you may want emptied.

HTH,
Bernie
MS Excel MVP

How can I copy a workbook that has formulas without copying the info in the cells?
I want to retain the formulas in the cells of the new workbook, but

want to delete the data in the cells, so I have a blank workbook, but
still have the formulas for the cells.

 

[HANKYPANK]

Bernie
Thanks for your quick reply
When I follow procedure described, everything is blank except the numbers in the cells
I want to delete the numbers without losing the formulas for each cell, so I can use the same workbook with its headers and labels and formulas for new info for the current year
Hank

----- Bernie Deitrick wrote: ----

HankyP

Select all cells (click the box above and to the left of cell A1) the
use Edit | Go To.... Special..., Constants, click OK, then hi
Delete. Note that this will also get rid of headers and labels, so i
you need to keep those, narrow done your initial selection to jus
cells that you may want emptied

HTH
Berni
MS Excel MV

How can I copy a workbook that has formulas without copying the inf in the cells
I want to retain the formulas in the cells of the new workbook, bu

want to delete the data in the cells, so I have a blank workbook, bu
still have the formulas for the cells

 

[Mark Graesser]

HANKYPANKY
You could define a name to the group of cells you want to clear

1) If all the cells are side by side then sweep them, if not, then hold your control key while clicking on all of the cells

2) Insert>Name>Define and enter a name. Hit O

3) Just above the upper left corner of your sheet is a gray rectangle with a down arrow in it. If you click on this a list of named areas should show up. Select the name you created

4) Hit delet

Good Luck
Mark Graesse
(e-mail address removed)

----- HANKYPANK wrote: ----

How can I copy a workbook that has formulas without copying the info in the cells
I want to retain the formulas in the cells of the new workbook, but want to delete the data in the cells, so I have a blank workbook, but still have the formulas for the cells.

 

[HANKYPANK]

Thanks, Mark.
But that method also erases the formulas for the cells. I want to keep the formulas and just delete the numbers.
Hank

 

[David McRitchie]

You may have to reenter some of your number constants as formulas,
or lose them. You can check what you have by
selecting all cells then using Edit, GoTo, and selecting
contants or formulas the items under formulas apply the
whichever of constants or formulas that you chose.

Now that I see Bernie's reply, and your reply to his would
modify it be Edit, GoTo, Special, (check) Constants, and
(check) Numbers -- don't check text nor the others

You would make a copy of the workbook and then run
a macro against all of the sheets to remove constants.
This could result in all of your formulas showing errors.

An example of removing formulas can be seen in
http://www.mvps.org/dmcritchie/excel/proper.htm#text
kind of the opposite of what you want.

You can go through the sheets with a macro, but this will
show you how to create a new sheet without numeric constants. (untested)

Sub RemoveNumberConstants()
'modified 2004-01-27 from
'Sub AllCellsToText()
'D.McRitchie, posted 2003-01-17 worksheet.functions
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Sheets(ActiveSheet.Name).Copy Before:=Sheets(ActiveSheet.Name)
On Error Resume Next 'In case no such cells in selection
For Each cell In Cells.SpecialCells(xlConstants, xlNumbers)
cell.formula = "" ' -- clear content
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

For information on installing/using macros see my getstarted.htm webpage.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How can I copy a workbook that has formulas without copying the info in the cells?
I want to retain the formulas in the cells of the new workbook, but want to delete the data in the cells, so I have a blank

workbook, but still have the formulas for the cells.