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 |
Internal links
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 |
Internal links
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 |
Internal links
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 |
Internal links
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 |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com