Don't forget to add in the protect and unprotect stuff. I removed that when I
was testing.

Casey wrote:

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