paste formula without workbook reference

[Thomas Wright]

when working out the details I often "perfect a formula in one workbook and
then paste it into another. However, I can not find a wyanot to paste the
formula with ref. to the first notebook. I then have to spend more time
deleting references. Its a bit of a hassle.

Is there a way not to paste the ref. to the first workbook?

Thanks

Tom

[Dave Peterson]

Maybe something like:

Workbooks("book2.xls").Worksheets("sheet1").Range("c4").Formula _
= Workbooks("book1.xls").Worksheets("sheet1").Range("e6").Formula





Thomas Wright wrote:
>
> when working out the details I often "perfect a formula in one workbook and
> then paste it into another. However, I can not find a wyanot to paste the
> formula with ref. to the first notebook. I then have to spend more time
> deleting references. Its a bit of a hassle.
>
> Is there a way not to paste the ref. to the first workbook?
>
> Thanks
>
> Tom

--

Dave Peterson

[keepITcool]

this one will copy the formula 'absolute',change to Formular1c1 for
relative copies.

In this context I mean following with absolute..
if you copy
from test.xls sheet1 a3 =sheet3!b6
to demo.xls sheet2 b6

you'll get =sheet3!b6

relative copy would get you =sheet3!c9

if you select more than 1 cell the destiantion.selection is
automatically resized.. neat!!!


Sub GetFormula()
Dim r As Range
On Error Resume Next
'turn off alerts in case sheet references are invalid
Application.DisplayAlerts = False
Set r = Application.InputBox("Select cell with Formula", Type:=8)

If Not r Is Nothing Then
Selection.Resize(r.Rows.Count, r.Columns.Count).Formula = r.Formula
End If
Application.DisplayAlerts = True

End Sub

have fun...



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Thomas Wright wrote :

> when working out the details I often "perfect a formula in one
> workbook and then paste it into another. However, I can not find a
> wyanot to paste the formula with ref. to the first notebook. I then
> have to spend more time deleting references. Its a bit of a hassle.
>
> Is there a way not to paste the ref. to the first workbook?
>
> Thanks
>
> Tom