Results 1 to 10 of 10
Thread: Copying formulas (2000)

20060502, 17:08 #1
 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?

20060502, 19:23 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,121
 Thanks
 2
 Thanked 439 Times in 362 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]

20060502, 20:25 #3
 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?

20060502, 20:46 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,121
 Thanks
 2
 Thanked 439 Times in 362 Posts
Re: Copying formulas (2000)
Hi Tal,
If you're using EditPaste SpecialFormulae, 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20060502, 22:12 #5
 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>

20061229, 14:24 #6
 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.

20061229, 14:40 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20061229, 20:22 #8
 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 formulaebutnotdatavalues as a useful and useable approach.

20061229, 20:58 #9
 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.

20061231, 06:14 #10
 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