ExcelBanter - View Single Post - Convert a UDF to it's value when copy sheet

Dave Peterson

I think I'd just copy the values to the new sheet:

Option Explicit
Sub CopySaveRFI()

Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet
'keeps all the formatting, page setup, etc the same
curWks.Copy
Set newWks = ActiveSheet

'but now copy as values
curWks.Cells.Copy
newWks.Cells.PasteSpecial Paste:=xlPasteValues

Application.Dialogs(xlDialogSaveAs).Show

End Sub

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