Internal links

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" <[email protected]> wrote in message news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message news:uTiF%[email protected]...
> 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
> news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:uTiF%[email protected]...
>> 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
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>