Convert a UDF to it's value when copy sheet

[Casey]

Hi,
I have a UDF that places the sheet name into Cell "I3" using th
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 an
convert all formulas to there values for distribution. But the UD
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 Su

 

[Bob Phillips]

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)

 

[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

 

[Dave Peterson]

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.

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)

 

[Dave Peterson]

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

 

[Casey]

Bob,
Thanks for the lesson. I'm pretty good with formulas but I have no idea
how this formula extracts the sheet name, the way the UDF did? I will
have to ponder this one for a while. I ran it through Tools>Formula
Auditing>Evaluate Formula so I think the light is coming on. Brilliant.
Thanks again.

 

[Casey]

Dave,
Thanks for jumping in. I haven't tried your solution yet partly because
Bob's popped up first and worked great and part because I wasn't sure,
you were sure, that your solution would work. I just wanted to thank
you for all the great answers you have given me and the answers I have
gleened from your other posts. You and Bob are both terrific.

 

[Casey]

Bob,
In my haste, I only verified that the formula worked, however it
appears to suffer the same malady as the UDF. It gives me a #VALUE
error. Something Dave said leads me to suspect that until the workbook
is saved the formula can't calc. That being said, using my code is
there way to exclude the "I3" cell from the conversion from formulas to
values or a way to insert the save process for the new workbook so the
formula will calculate and then do the conversion?

 

[Dave Peterson]

If you're going to loop through the cells...

For Each c In d
With c
.Value = .Value
End With
Next c

could look more like:

For Each c In d
With c
If UCase(.Formula) _
Like UCase("*" & "=MID(CELL(""filename""," & "*") Then
'do nothing
Else
.Value = .Value
End If
End With
Next c

Make sure you include enough to check the correct formula.