Removing Links and Formulas
 

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

Removing Links and Formulas
 

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

Removing Links and Formulas
 

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

Removing Links and Formulas
 

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