Erasmus Data Service Centre (EDSC) manuals

Replace all formulas in Excel

Once you are finished downloading you might end up with a lot of sheets containing formulas. If you want to replace your formulas by values in all sheets at once you can use this macro.

Make sure you don't have any filters enabled in your dataset.

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
End Sub

Sub All_Cells_In_All_WorkSheets_2()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
End Sub