Copy Formula That References Another Sheet

J

JR573PUTT

How can I copy a formula that references another sheet to a new file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?
 
D

Dave Peterson

Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both worksheets)
 
V

vezerid

Double-click the cell to edit. Copy the formula from the formula bar,
i.e. copy the text of the formula and paste it.

HTH
Kostis Vezerides
 
J

JR573PUTT

Thanks that worked, but not for an array formula where I commit th
formula by hitting ctrl shift enter.

Is there a way to copy an array formula that references a sheet
 
D

Dave Peterson

You could use code...

Option Explicit
Sub testme()

Dim FromCell As Range
Dim ToCell As Range

Set FromCell = Workbooks("book2.xls").Worksheets("sheet1").Range("a1")
Set ToCell = Workbooks("Book1.xls").Worksheets("sheet1").Range("a1")

If FromCell.HasFormula Then
If FromCell.HasArray Then
ToCell.FormulaArray = FromCell.FormulaArray
Else
ToCell.Formula = FromCell.Formula
End If
End If
End Sub
 
Top