Remove formulas from all worksheets in Excel using VBA

Question

I'm having trouble getting Excel to remove formulas from cells and keep simply the value (in case there is a number). The fact that the various spreadsheets also contain pivot tables and GETPIVOTDATA cells is the cause of the issue.

I am currently trying this code but it only works on normal spreadsheets:

Sub fun()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pc As PivotCell

For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    With ws.UsedRange
        .Value = .Value
    End With
Next ws


For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Value = pi.Value
            Next pi
        Next pf
    Next pt
Next ws

End Sub

Could you help me to adapt the code so that every cell will be set to value?

Answer 1

Try this :

Option Explicit

Sub test1()
    Dim ws As Worksheet, a, area As String
    For Each ws In ThisWorkbook.Worksheets
        a = ws.UsedRange
        area = ws.UsedRange.Address
        ws.Cells.ClearContents
        ws.Range(area) = a
    Next
End Sub