Convert a UDF to it's value when copy sheet
 

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

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=48820

Convert a UDF to it's value when copy sheet
 

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

Convert a UDF to it's value when copy sheet
 

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

Convert a UDF to it's value when copy sheet
 

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

Convert a UDF to it's value when copy sheet
 

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

Convert a UDF to it's value when copy sheet
 

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 ToolsFormula
AuditingEvaluate Formula so I think the light is coming on. Brilliant.
Thanks again.


--
Casey


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

Convert a UDF to it's value when copy sheet
 

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


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

Convert a UDF to it's value when copy sheet
 

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?


--
Casey


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

Convert a UDF to it's value when copy sheet
 

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.

Casey wrote:

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?

--
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