Macro taking out all external references
 

Hi guys,

I'm wondering how to create a macro that will convert into "values" all the
cells that are referencing to other workbooks (or even other worksheets)
To put it differently, the only formulas I would like to keep are those that
refer to the sheet on which they are written.

=C5 (would like to keep)
=Sheet1!F14 (to convert into "value")
=[Other_Workbook.xls]Sheet1!B15 (to convert into "value")

Could you please help me with this?
Thanks!

P.S. If it's not to complicated, it would be really excellent to have not
only the formula that will do this operation on the active sheet, but also
(another code) to repeat the whole procedure on all the worksheets...

Macro taking out all external references
 

I would really apprichiate some help on this specific macro too


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=487931

Macro taking out all external references
 

Only lightly tested.
Assumes "!" & "[" can only exist in formulas that reference other sheets.

Sub RemoveLinks(ws As Worksheet, bAll As Boolean)
Dim rng As Range
Dim cel As Range

'errors if no formulas on sheet
On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
'does not return range if formulas in 8000+ areas of cells
On Error GoTo 0

If Not rng Is Nothing Then
For Each cel In rng
If bAll Then
'remove links to other sheets
If InStr(cel.Formula, "!") Then
cel.Value = cel.Value
End If
Else
'remove links to other workbooks
If InStr(cel.Formula, "[") Then
cel.Value = cel.Value
End If
End If
Next

End If


End Sub

Sub test()
Dim ws As Worksheet
Dim bRemoveAll As Boolean

'true all links, false only workbook links
bRemoveAll = True

'activesheet only
Set ws = ActiveSheet
'RemoveLinks ws, bRemoveAll

' process all sheets
For Each ws In ActiveWorkbook.Worksheets
RemoveLinks ws, bRemoveAll
Next

End Sub

Regards,
Peter T

"markx" wrote in message
...
Hi guys,

I'm wondering how to create a macro that will convert into "values" all
the
cells that are referencing to other workbooks (or even other worksheets)
To put it differently, the only formulas I would like to keep are those
that
refer to the sheet on which they are written.

=C5 (would like to keep)
=Sheet1!F14 (to convert into "value")
=[Other_Workbook.xls]Sheet1!B15 (to convert into "value")

Could you please help me with this?
Thanks!

P.S. If it's not to complicated, it would be really excellent to have not
only the formula that will do this operation on the active sheet, but also
(another code) to repeat the whole procedure on all the worksheets...

Macro taking out all external references
 

Is it not possible to use "BreakLink" ????

Grtz,
Rody



"Peter T" <peter_t@discussions schreef in bericht
...
Only lightly tested.
Assumes "!" & "[" can only exist in formulas that reference other sheets.

Sub RemoveLinks(ws As Worksheet, bAll As Boolean)
Dim rng As Range
Dim cel As Range

'errors if no formulas on sheet
On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
'does not return range if formulas in 8000+ areas of cells
On Error GoTo 0

If Not rng Is Nothing Then
For Each cel In rng
If bAll Then
'remove links to other sheets
If InStr(cel.Formula, "!") Then
cel.Value = cel.Value
End If
Else
'remove links to other workbooks
If InStr(cel.Formula, "[") Then
cel.Value = cel.Value
End If
End If
Next

End If


End Sub

Sub test()
Dim ws As Worksheet
Dim bRemoveAll As Boolean

'true all links, false only workbook links
bRemoveAll = True

'activesheet only
Set ws = ActiveSheet
'RemoveLinks ws, bRemoveAll

' process all sheets
For Each ws In ActiveWorkbook.Worksheets
RemoveLinks ws, bRemoveAll
Next

End Sub

Regards,
Peter T

"markx" wrote in message
...
Hi guys,

I'm wondering how to create a macro that will convert into "values" all
the
cells that are referencing to other workbooks (or even other worksheets)
To put it differently, the only formulas I would like to keep are those
that
refer to the sheet on which they are written.

=C5 (would like to keep)
=Sheet1!F14 (to convert into "value")
=[Other_Workbook.xls]Sheet1!B15 (to convert into "value")

Could you please help me with this?
Thanks!

P.S. If it's not to complicated, it would be really excellent to have not
only the formula that will do this operation on the active sheet, but
also
(another code) to repeat the whole procedure on all the worksheets...

Macro taking out all external references
 

I think Break Links was introduced in XL2002. I only have XL2000 to hand but
from memory (?) that does not break links to cells in other sheets within
the same workbook, which was one of the OP's requirements.

Regards,
Peter T

"Rody Meulman" wrote in message
...
Is it not possible to use "BreakLink" ????

Grtz,
Rody



"Peter T" <peter_t@discussions schreef in bericht
...
Only lightly tested.
Assumes "!" & "[" can only exist in formulas that reference other
sheets.

Sub RemoveLinks(ws As Worksheet, bAll As Boolean)
Dim rng As Range
Dim cel As Range

'errors if no formulas on sheet
On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
'does not return range if formulas in 8000+ areas of cells
On Error GoTo 0

If Not rng Is Nothing Then
For Each cel In rng
If bAll Then
'remove links to other sheets
If InStr(cel.Formula, "!") Then
cel.Value = cel.Value
End If
Else
'remove links to other workbooks
If InStr(cel.Formula, "[") Then
cel.Value = cel.Value
End If
End If
Next

End If


End Sub

Sub test()
Dim ws As Worksheet
Dim bRemoveAll As Boolean

'true all links, false only workbook links
bRemoveAll = True

'activesheet only
Set ws = ActiveSheet
'RemoveLinks ws, bRemoveAll

' process all sheets
For Each ws In ActiveWorkbook.Worksheets
RemoveLinks ws, bRemoveAll
Next

End Sub

Regards,
Peter T

"markx" wrote in message
...
Hi guys,

I'm wondering how to create a macro that will convert into "values" all
the
cells that are referencing to other workbooks (or even other
worksheets)
To put it differently, the only formulas I would like to keep are those
that
refer to the sheet on which they are written.

=C5 (would like to keep)
=Sheet1!F14 (to convert into "value")
=[Other_Workbook.xls]Sheet1!B15 (to convert into "value")

Could you please help me with this?
Thanks!

P.S. If it's not to complicated, it would be really excellent to have
not
only the formula that will do this operation on the active sheet, but
also
(another code) to repeat the whole procedure on all the worksheets...