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.

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

"Chaplain Doug" a écrit dans le
message de news: ...
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.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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


"Ardus Petus" wrote:

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

"Chaplain Doug" a écrit dans le
message de news: ...
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.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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

"Chaplain Doug" a écrit dans le
message de news: ...
Thank you Ardus. From where do you write? Your name sounds Latvian.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Ardus Petus" wrote:

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

"Chaplain Doug" a écrit dans le
message de news: ...
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.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org