Copy Formulas

[AJ]

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

 

[Tom]

AJ-

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

Tom

 

[mrmac]

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.

 

[Bob Umlas]

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_results.asp?select_cartid=395


Bob Umlas
Excel MVP

 

[Tom Ogilvy]

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

 

[Ron de Bruin]

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

 

[David McRitchie]

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

Sub CopyFormula()
'Chip Pearson, microsoft.public.excel.worksheet.functions, 2000/05/02
' http://groups.google.com/groups?hl=en&newwindow=1&th=4831aec5cbe19367&rnum=1
' http://groups.google.com/groups?as_umsgid=OWeRetUjBHA.2212@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 Autpen
'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 Autpen use Workbook_Open in the ThisWorklbook
' when you need to fire off a macro when opening with code.


--