I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

AJ-

Have you tried doing a pastespecial as formulas? I hope
that is what you are looking for.

Tom
-----Original Message-----
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

.

Copy the formula while in editing mode (double-click the
cell and select the formula). Move to the other sheet and
do the reverse to paste. This will copy the formulas as
pure text.
-----Original Message-----
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

.

Select a blank worksheet as well as the sheet containing the formulas to
copy (putting the 2 sheets in group mode)
Select the cells with the formulas to copy. Use Edit/Fill/Across Worksheets.
This puts exact copy into the blank sheet.
Activate the new sheet (NOT in group mode), Cut/Paste to the new location.
Put the 2 sheets back in group mode, keeping the new sheet active. Select
the cells in the new location, use Edit/Fill/Across Worksheets. Your
formulas have been copied into the new location and the references have not
changed.
Delete the "new" sheet.

A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:

http://www.iil.com/str_link_all_resu...ect_cartid=395


Bob Umlas
Excel MVP

"AJ" wrote in message
...
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

worksheets("AnotherSheetName").Range("A1").Formula = _
worksheets("originalSheet").Range("B9").formula

--
Regards,
Tom Ogilvy


"Tom" wrote in message
...
AJ-

Have you tried doing a pastespecial as formulas? I hope
that is what you are looking for.

Tom
-----Original Message-----
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

.

You can make it text before you copy

Sub test()
For Each cell In Selection.Cells.SpecialCells(xlCellTypeFormulas)
cell.Value = "'" & cell.Formula
Next
End Sub

And remove the ' after the copy

Sub test2()
Selection.Value = Selection.Value
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AJ" wrote in message ...
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ

If you want to do this often Chip Pearson posted code for a macro
to be used as a right click menu. You have to install it into your
workbook. Refer to the links so you install code into your
Auto_Open macro.

Sub CopyFormula()
'Chip Pearson, microsoft.public.excel.worksheet.functions, 2000/05/02
' http://groups.google.com/groups?hl=e...19367&rnum= 1
' http://groups.google.com/groups?as_u...12@tkmsftngp05
Dim x As New DataObject
x.SetText ActiveCell.Formula
x.PutInClipboard
End Sub

Sub PasteFormula()
On Error Resume Next
Dim x As New DataObject
x.GetFromClipboard
ActiveCell.Formula = x.GetText
End Sub

'--- add this code to your Auto_Open
'Chip Pearson via Drew Paterson -- 2001-04-13 misc
'--http://groups.google.com/groups?threadm=uiqh89AxAHA.1620%40tkmsftngp05
Application.CommandBars("Cell").Reset 'was not in 2001-04-13 posting
With Application.CommandBars("Cell").Controls
With .Add
.Caption = "C&opy Formula"
.OnAction = ThisWorkbook.Name & "!CopyFormula"
.Tag = "Formulas" 'cControlTag
.BeginGroup = True
End With

With .Add
.Caption = "P&aste Formula"
.OnAction = ThisWorkbook.Name & "!PasteFormula"
.Tag = "Formulas2" 'cControlTag
End With
End With
'instead of Auto_Open use Workbook_Open in the ThisWorklbook
' when you need to fire off a macro when opening with code.


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

"AJ" wrote in message ...
I have some values which are derived from a formula. I am
trying to paste those values on another sheet and want
then to use the exact same formula . how can do that
without having absolte cell reference?
Thanks
AJ