Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
does any one know how to convert a scanned sheet to a work sheet | Charts and Charting in Excel | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
providing a sheet-copy event or copy CustomProperties | Excel Programming |