Convert to Values
 

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

Convert to Values
 

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


"Ananth" wrote:

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

Convert to Values
 

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
--
Gary''s Student - gsnu200860


"Ananth" wrote:

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