Anyone written anything that will find all Internal links and paste special
values? Because an internal link can be =C2 or =Sheet1!C2 I see you could
look for the "!", but what about the other link =C2?

Cheers,

Job

Job,

Do you consider a formula like:

=SUM(A1:A3)

an internal link?

Would converting all formulas to values work, or do you need to still have formulas work?

HTH,
Bernie
MS Excel MVP


"Job" wrote in message ...
Anyone written anything that will find all Internal links and paste special values? Because an
internal link can be =C2 or =Sheet1!C2 I see you could look for the "!", but what about the other
link =C2?

Cheers,

Job

That's the problem, I still need all formulas. I only need to paste the
values where it's an actual link as mentiond before. Any sums or averages
max min etc should be left alone.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Job,

Do you consider a formula like:

=SUM(A1:A3)

an internal link?

Would converting all formulas to values work, or do you need to still have
formulas work?

HTH,
Bernie
MS Excel MVP


"Job" wrote in message
...
Anyone written anything that will find all Internal links and paste
special values? Because an internal link can be =C2 or =Sheet1!C2 I see
you could look for the "!", but what about the other link =C2?

Cheers,

Job

Job,

Try the macro below on a copy of a sheet - as written, it will only operate on the activesheet, but
it would be easy to loop through all sheets.

Run the macro, and if any formulas come up in the msgbox that should have stayed formulas, post
back. For longer term use, remove the msgbox lines.

HTH,
Bernie
MS Excel MVP

Sub RemoveLinksFromCellsButNotFormulas2()
Dim myCell As Range
Dim myAddress As String
Dim myLink As Range

For Each myCell In ActiveSheet.Cells. _
SpecialCells(xlCellTypeFormulas)
myAddress = Replace(myCell.Formula, "=", "")
On Error GoTo NotLink
Set myLink = Range(myAddress)
MsgBox "Removing link " & myCell.Formula & _
" from cell " & myCell.Address
myCell.Value = myCell.Value

NotLink:
Resume Done1
Done1:
Next
End Sub



"Job" wrote in message ...
That's the problem, I still need all formulas. I only need to paste the values where it's an
actual link as mentiond before. Any sums or averages max min etc should be left alone.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Job,

Do you consider a formula like:

=SUM(A1:A3)

an internal link?

Would converting all formulas to values work, or do you need to still have formulas work?

HTH,
Bernie
MS Excel MVP


"Job" wrote in message ...
Anyone written anything that will find all Internal links and paste special values? Because an
internal link can be =C2 or =Sheet1!C2 I see you could look for the "!", but what about the
other link =C2?

Cheers,

Job

Hey Bernie,

Thanks this works great!

Job

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Job,

Try the macro below on a copy of a sheet - as written, it will only
operate on the activesheet, but it would be easy to loop through all
sheets.

Run the macro, and if any formulas come up in the msgbox that should have
stayed formulas, post back. For longer term use, remove the msgbox lines.

HTH,
Bernie
MS Excel MVP

Sub RemoveLinksFromCellsButNotFormulas2()
Dim myCell As Range
Dim myAddress As String
Dim myLink As Range

For Each myCell In ActiveSheet.Cells. _
SpecialCells(xlCellTypeFormulas)
myAddress = Replace(myCell.Formula, "=", "")
On Error GoTo NotLink
Set myLink = Range(myAddress)
MsgBox "Removing link " & myCell.Formula & _
" from cell " & myCell.Address
myCell.Value = myCell.Value

NotLink:
Resume Done1
Done1:
Next
End Sub



"Job" wrote in message
...
That's the problem, I still need all formulas. I only need to paste the
values where it's an actual link as mentiond before. Any sums or
averages max min etc should be left alone.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Job,

Do you consider a formula like:

=SUM(A1:A3)

an internal link?

Would converting all formulas to values work, or do you need to still
have formulas work?

HTH,
Bernie
MS Excel MVP


"Job" wrote in message
...
Anyone written anything that will find all Internal links and paste
special values? Because an internal link can be =C2 or =Sheet1!C2 I
see you could look for the "!", but what about the other link =C2?

Cheers,

Job