Removing Links and Formulas

[Guest]

Excel 2003. I have workbooks in which there are internal links (cell
formulas and references to labeled cells in other sheets). I would like to
"freeze" these before programmatically copying these sheets to another
workbook. How can I resolve the internal links and formulas so that the
references and formulas are no longer there, only the actual values the
references or formulas produce? I need to do this programmatically. Thanks
for the help and God bless.

 

[Ardus Petus]

Sub freezeFormulas()
Dim ws As Worksheet
Dim rngFormulas As Range
Dim rng As Range
For Each ws In Worksheets
On Error Resume Next
Set rngFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
If Not rngFormulas Is Nothing Then
For Each rng In rngFormulas
rng.Value = rng.Value
Next rng
End If
Next ws
End Sub

HTH

 

[Guest]

Thank you Ardus. From where do you write? Your name sounds Latvian.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Sub freezeFormulas()
Dim ws As Worksheet
Dim rngFormulas As Range
Dim rng As Range
For Each ws In Worksheets
On Error Resume Next
Set rngFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
If Not rngFormulas Is Nothing Then
For Each rng In rngFormulas
rng.Value = rng.Value
Next rng
End If
Next ws
End Sub

HTH

 

[Ardus Petus]

That's a nickname, after the place where I live (Ardus)

Thank you Ardus. From where do you write? Your name sounds Latvian.