Ken,

Try converting all the formulas to strings, then copying the sheet, and
converting the strings back to formulas in both the original and copied
sheets. Example macro below. (Or have your Sheet1 have the formulas as
strings -cell contents with a single quote then the formula string, and
don't bother with the conversion except for in the copy.)

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myC As Range
Dim myS1 As Worksheet
Dim myS As Worksheet


With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set myS1 = Workbooks("Name.xls").Worksheets("Sheet1")
For Each myC In myS1.Cells.SpecialCells(xlCellTypeFormulas)
myC.Value = "'" & myC.Formula
Next myC

myS1.Copy befo=ThisWorkbook.Sheets(1)
Set myS = ThisWorkbook.Sheets(1)

For Each myC In myS1.Cells.SpecialCells(xlCellTypeConstants)
If Mid(myC.Value, 1, 1) = "=" Then myC.Formula = Mid(myC.Value, 2)
Next myC

For Each myC In myS.Cells.SpecialCells(xlCellTypeConstants)
If Mid(myC.Value, 1, 1) = "=" Then myC.Formula = Mid(myC.Value, 2)
Next myC

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub


"Ken Valenti" wrote in message
...
Sorry for the confusion.

Sheet1 resides in a seperate workbook (with the macros) and is copied to
the
activeworkbook, which has the data and range names the formulas require.

Sheet1 has no range names defined, only formulas referring to range names
that don't exist in the workbook.


"Kenneth Hobson" wrote:


That is adding a copy of the sheet to the same workbook. Are you saying
that the names in the formulas do not work in the new sheet?

If you are using relative names, you may need to add $ before the names
to get what you expect.


--
Kenneth Hobson
------------------------------------------------------------------------
Kenneth Hobson's Profile:
http://www.thecodecage.com/forumz/member.php?userid=413
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=105840