Replace formulas with values

[BW]

I'm trying to use vba to replace all formula references to a particular
sheet with values. Can someone please point me in the right direction?

Thanks in advance

BW

 

[Frank Kabel]

Hi
copy the cells and insert the values with 'Edit - Paste Special -
values'

 

[BW]

More specifically, I have to find any formula references in my workbook to
my "SourceData" sheet and replace those formulas with the actual value. I
don't think the manual find/replace function will work in this instance.

BW

 

[Harald Staff]

Hi BW

Assuming "Source data sheet" is another workbook, use a macro like this:

Sub test()
Dim R As Range
Dim W As Worksheet
On Error Resume Next
For Each W In ActiveWorkbook.Worksheets
For Each R In W.Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(R.Formula, "[") > 0 Then
R.Value = R.Value
End If
Next
Next
End Sub

HTH. Best wishes Harald

 

[Dave Peterson]

I renamed the worksheet to a unique string. Then looked for that unique string
in the formulas.

This way if you have:
="the value from SourceData! is too large for date: " _
& text(today(),"mm/dd/yyyy")

(not much chance, but why take it?)


Option Explicit
Sub testme()

Dim SourceDataWks As Worksheet
Dim OldName As String
Dim NewName As String

Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

NewName = String(31, "9")

Set SourceDataWks = Worksheets("sourcedata")
OldName = SourceDataWks.Name
SourceDataWks.Name = NewName

myStr = "'" & NewName & "'"

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = SourceDataWks.Name Then
'do nothing
Else
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If myRng Is Nothing Then
'do nothing, no formulas
Else
For Each myCell In myRng.Cells
If InStr(1, myCell.Formula, myStr, _
vbTextCompare) > 0 Then
myCell.Value = myCell.Value
End If
Next myCell
End If
End With
End If
Next wks

SourceDataWks.Name = OldName

End Sub

If you have a worksheet named "99999999999999999..." 31 times, then use a
different name.

 

[Dave Peterson]

And I assumed that SourceData was in the same workbook.

 

[BW]

Dave, you da man! Excatly what I was looking for. Thanks. I'll modify it so
that only the active sheet links are modified or create some sort of list so
that the user can select which sheet to remove the links from. Thanks again
sir.

BW