But this will suffer the same malady.

The formula will be an error before the workbook is saved. And Casey converts
to values before displaying the saveas dialog.



Bob Phillips wrote:

That is because the UDF doesn't get copied over as well.

Why not just use a formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

that will calculate when you save the new workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Casey" wrote in
message ...

Hi,
I have a UDF that places the sheet name into Cell "I3" using the
following formula:

Sheetname (A1) in "I3"

Here is the UDF:
Public Function SheetName(ref) As String
SheetName = ref.Parent.Name
End Function

I then use a routine tied to a command button to copy the sheet and
convert all formulas to there values for distribution. But the UDF
formula doesn't get converted to it's value. Would appreciate any help.
Here's my Copy code:
Sub CopySaveRFI()
Dim c As Range
Dim d As Range
ActiveSheet.Copy
ActiveSheet.Unprotect ("geekk")
Set d = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
Value = .Value
End With
Next c
ActiveSheet.Protect ("geekk")
Application.Dialogs(xlDialogSaveAs).Show
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=488205


--

Dave Peterson