Dear All,

Normally I have with many formula but when I forward the file i
convert all formulas with their values (with the help of Paste
Special) but I want to replace all formulas with values except
subtotal & sum formulas.

Can anyone help me.

Thnx in advance.

Hi Shaqil,

Try something like:

'============>>
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range

Set WB = Workbooks("MyBook1.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
Set Rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell
If InStr(1, .Formula, "Sum", vbTextCompare) = 0 _
And InStr(1, .Formula, "SubTotal", _
vbTextCompare) = 0 Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<============


---
Regards,
Norman



"shaqil" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear All,
>
> Normally I have with many formula but when I forward the file i
> convert all formulas with their values (with the help of Paste
> Special) but I want to replace all formulas with values except
> subtotal & sum formulas.
>
> Can anyone help me.
>
> Thnx in advance.
>