Need VBA to delete all link in sheet except sum formula

[loveguy1977]

Dear Sir,

In my worksheet, there is alot of tables and alot of cell that are linked to other sheet or to other cells. I need to make all cell as value using macro except cells that have SUM, SUMTOTAL formula

Thank you

[streub]

Why can't SUM and SUMTOTAL be a value?

[mancubus]

hi. try this in when desired sheet is active sheet.



Sub Convert_to_Value()


Dim cll As Range

For Each cll In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If Left(cll.Formula, 4) <> "=SUM" And Left(cll.Formula, 9) <> "=SUBTOTAL" Then
cll.Value = cll.Value
End If
Next cll


End Sub