David

Well, the formulas are not the same everywhere, but the function used in the formulas are the same
e.g. some of the formulas are =SUM(A1:A3), =SUM(B4:B9), SUM(D20:D31), etc. But the common thing in all the formulas is that all the formulas that I need replaced have the SUM() in them. I need these replaced with its resultant values. But at the same time, I need to leave every other cell in the worksheet as it is. I hope I am clear in what I am saying

Here is what I currently have.... its in VB 6.

' wkb is source workboo
' wkb1 is destination workboo
' I copy each worksheet from source to destination and then loop thru' the cells in the destination to check the formulas in them and perform the replace

For Each wks In wkb.Worksheet
isHidden = wks.Visibl

wks.Visible = xlSheetHidde
wks.Activat
Cells.Selec
Selection.Cop
wkb1.Worksheets(i).Activat

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals

Range("A1").Selec
wkb1.Worksheets(i).Name = wks.Nam

' Loop thru' each cell to strip off the formula for specific cells
For Each cel In wkb1.Worksheets(i).UsedRang
If cel.HasFormula The
curcell = cel.Formul
Els
curcell = cel.Valu
End I

cel.Cop

Range(cel.Address).Selec

If InStr(0, curcell, "=SUM(", 1) 0 The
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Els
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
End I

Nex

wkb1.Worksheets(i).Visible = isHidde

i = i +
Nex

Thanks
- Vija