Function to replace formulas with values

[lynxbci]

Hi,
I have a workbook, multiple sheets, and within each sheet there are multiple cells with formulas in. I want to write a function to replace any cell which has a formula, with the value in the cell. Tried find and replace macro, but seems very messy. Any suggestions please?
Thanks in advance

 

[Andrew Poulsom]

You can't use a function, but here's a Sub to try:

Sub Test()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Cells.Copy
            .Range("A1").PasteSpecial xlPasteValues
        End With
    Next ws
End Sub

 

[VoG]

Try

Sub FtoVal()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
    With ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        .Value = .Value
    End With
    On Error GoTo 0
Next ws
End Sub

 

[lynxbci]

Hi,

Can i thank you, and add a slight change, I only want to replace formulas that begin with "=cc.*" and leave all other formulas in tact.

Thanks again

 

[VoG]

Try

Sub FtoVal()
Dim ws As Worksheet, c As Range
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        If c.Formula Like "=cc.*" Then c.Value = c.Value
    End With
    On Error GoTo 0
Next ws
End Sub

 

[lynxbci]

Thank you so much