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

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


"Casey" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

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



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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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

[Dave Peterson]

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

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

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