I had to do some real quick budgeting work today, and needed to build a template to work from.Â The easiest way was to grab a heavily linked workbook we had, and convert all the links to values so that I could work some different assumptions on it.Â The thing is that I didn't want to just copy and past values, as I wanted all the other formulas to work, I just didn't want to point to external files any more.
I ended up coding this to work as follows:
Dim ws As Worksheet
Dim cl As Range
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Application.StatusBar = _
"Removing external links from " & ws.Name
Set cl = .Find(":\", LookIn:=xlFormulas)
If Not cl Is Nothing Then
cl.Value = cl.Value
Set cl = .FindNext(cl)
Loop While Not cl Is Nothing
.ScreenUpdating = True
.StatusBar = False
Worked fairly well, and quickly, giving me the feedback of how far it had progressed.
I'm curious to any comments on this, though.Â Is there an easier way?