On Thu, 24 Jul 2008 01:31:08 GMT, "lonnierudd via OfficeKB.com" <u11209@uwe
wrote:

It's what he wants, so if there's somebody with an idea, I would be grateful.

lonnierudd wrote:
If nothing else it seems like a lot of work for no gain. I'll speak to him
about it, but he isn't likely to tell me why, unless he thinks VBA is magic
and the VERY long and cryptic formulas detracted from what he wanted to see.

You might want to tell your supervisor that doing what he asked will in all
probability slow down the execution of the spreadsheets dramatically.
[quoted text clipped - 11 lines]
take a worksheet and move it into VBA without doing it one thing at a
time?

I'd like to go on record as saying this is a bad idea. But it sounds like
you're stuck, so here's something. This is a simple proof of concept macro
and it will need some significant adjustments to apply to your actual
situation.

Sub MakeFormulaCode()

Dim rCell As Range
Dim sResult As String

sResult = "With Workbooks(""MyBook.xls"").Sheets(""Sheet1"")" &
vbNewLine

For Each rCell In Sheet1.UsedRange.Cells
If rCell.HasFormula Then
sResult = sResult & vbTab & _
".Range(""" & rCell.Address & _
""").Value = [" & rCell.Formula & "]" & vbNewLine
End If
Next rCell

sResult = sResult & "End With"

Debug.Print sResult

End Sub

This will generate code that you can paste into a macro. All it does is
record which formulas go in which cells and executes the formula in code and
puts the result in the cell. In addition to additional sheets, you'll have
to figure out how to handle formulas that return errors. Anyway, good luck
and I'm glad I don't have to do this (but I'm still sympathetic).
--
Dick