Convert to Values

[Ananth]

I have a workbook that has 40 tabs. In each of the tabs there are formulas
referenced to a tab named “DBASE†, which is one among the 40 tabs.

Is it possible to convert only those “DBASE†reference formulas wherever
referenced in all the 39 tabs into values by a single action using a macro.
All the tabs have data from Range A2 to AG4000

 

[Jbm]

Ananth,
You should probably save first, but try this:

Sub Tester()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
With sht.Range("A2:AG4000")
..Copy
..PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
Next sht
End Sub

 

[Gary''s Student]

Try:

Sub noDBASE()
Dim w As Worksheet, r As Range, s As String
For Each w In Worksheets
If w.Name <> "DBASE" Then
w.Activate
For Each r In ActiveSheet.UsedRange
s = r.Formula
If InStr(s, "DBASE") <> 0 Then
r.Copy
r.PasteSpecial Paste:=xlPasteValues
End If
Next
End If
Next
End Sub