Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying formulas (2000)

    I want to copy formulas ONLY . When I use Paste Special, and check only Formulas, the content also copies.
    Conversely, when I select the new content + formulas and use Clear, Content Only, the formulas are also deleted.
    How do I win this?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Copying formulas (2000)

    Hi Tal,

    If you're copying formulae, then they'll more or less immediately recalculate and show the new results.
    If you're using:
    . absolute cell referencing (eg =$A$1) then the copied formula will still be pointing to the original source cell, and so will still show the original value.
    . relative cell referencing (eg =A1) then the copied formula will still be pointing to a new source cell, and so will show a value based on the new source cell's contents.
    . a mix of absolute and relative cell referencing (eg =A$1or =$A1 or =$A$1+B1) then the copied formula will still be pointing to the corresponding mixture, and so will show a value based on that.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formulas (2000)

    I go to my section with contents and formula, and selecting the entire section.
    Then I go to an entirely blank section (to be populated later) and using PASTE SPECIAL, FORMULA and pasting. I only want the formulae, but the contents are pasting as well. How do I only paste the formulae?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Copying formulas (2000)

    Hi Tal,

    If you're using Edit|Paste Special|Formulae, then formulae is all you'll get. Of course, if some of the cells you've copied have values in them, they're treated as formuale too.

    To copy only cells that have formulae, you'd need a macro.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying formulas (2000)

    Does something like this work?

    Steve

    <pre>Option Explicit
    Sub CopyJustFormulas()
    Dim rCell As Range
    Dim rArea As Range
    Dim lRow As Long
    Dim iCol As Integer
    Dim rSelect As Range
    Dim rDest As Range
    On Error Resume Next
    Set rSelect = Selection.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If rSelect Is Nothing Then
    MsgBox "No formula have been selected"
    Exit Sub
    End If
    Set rDest = Application.InputBox( _
    "Where do you want to copy?", Type:=8)

    iCol = rDest.Column - Selection.Column
    lRow = rDest.Row - Selection.Row
    For Each rArea In rSelect
    For Each rCell In rArea
    rCell.Offset(lRow, iCol).FormulaR1C1 = _
    rCell.FormulaR1C1
    Next
    Next

    Set rDest = Nothing
    Set rSelect = Nothing
    Set rArea = Nothing
    Set rCell = Nothing
    End Sub</pre>


  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formulas (2000)

    > if some of the cells you've copied have values in them, they're treated as formuale too.
    (Excel 97, but probably other versions)
    This is bugging me and I seek clarification.
    What the heck is the use of "Formulas" as an option?
    To test the paste Special command I loaded a set of cells with data (using custom lists to generate lost of days and months). Each cell is now occupied by raw data.
    I went to another sheet and copied a block of cells that held (a) raw numeric data values ([img]/forums/images/smilies/cool.gif[/img] formulae prefaced by an equals sign empty cells.
    Back in the first sheet I chose Edit, paste Special and checked on the Formulas button.

    It appears that
    (1) Where the original cells (copied to the clipboard) contained formulae, I receive formulae in the target cells
    (2) Where the original cells (copied to the clipboard) contained raw numeric data , I receive raw numeric data in the target cells
    (3) Where the original cells (copied to the clipboard) were vacant, the target cells were cleared of data and formulae.

    That is, the "formulas" option seems to be indistinct from the regular Edit, Paste.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying formulas (2000)

    No, it is not the same. Edit | Paste Special > Formulas does *not* copy formatting, while Edit | Paste does. So you can use this option to copy both values and formulas to another range without disturbing the existing formatting of the target range.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formulas (2000)

    > copy both values and formulas
    Got it.
    So "Copy formats" does just that. No values, no formulae.
    "Copy values" does just that. No formulae.
    But "Copy formulas" copies both formulae and values.
    I think it is a terminology question. We see "Copy values" and know that we won't get formulae, so we see "Copy formulas" and suppose that we won't get values.
    Better it had been written "Copy Contents", rather than "Copy formulas", which was, I suspect, the thrust of the initial post in this thread.

    FWIW my most frequent use of Paste Special is to obtain frozen values; after that, to obtain formatting.
    Only recently have I found uses for formulae-but-not-data-values as a useful and useable approach.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying formulas (2000)

    The "formula" of a cell that has a value in it is the value itself.

    Just select a cell and look in the "formula bar"

    The paste values converts the contents to the "Values", the "paste formulas" keeps the "formula"

    The "contents" implies the formatting as well as the formula..

    Steve.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying formulas (2000)

    Are you by any chance expecting the cell to display the formula, and not the calculated value? Although this feature is available on a sheet by sheet basis, I don't believe it is selectable on a cell by cell basis. However, I'm certain that with a little thought a User defined function could be developed to display the formula of a referenced cell.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •