Ask a Question Sign up for Free 138 Experts currently online. Ask Questions for Free!

COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS - Microsoft Excel

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 ...

Results 1 to 6 of 6

  1. #1

    COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

    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
  2. #2

    Re: COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

    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

    "HANKYPANK" <microsoft.com> wrote in message
    news:com... 
    in the cells? 
    want to delete the data in the cells, so I have a blank workbook, but
    still have the formulas for the cells.


    Bernie
  3. #3

    Re: COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

    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

    "HANKYPANK" <microsoft.com> wrote in messag
    news:com.. 
    in the cells 
    want to delete the data in the cells, so I have a blank workbook, bu
    still have the formulas for the cells



    HANKYPANK
  4. #4

    RE: COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

    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
    co

    ----- 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.
    Mark
  5. #5

    RE: COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

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

    Re: COPY WORKBOOK-RETAIN FORMULAS-EMPTY CELLS

    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

    "HANKYPANK" <microsoft.com> wrote in message news:com... 
    workbook, but still have the formulas for the cells.


    David

Similar Threads

  1. COUNTA on cells containing formulas
    By Ken in forum Excel Worksheet Functions
    Replies: 5
    Last Post: 07-08, 12:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100