Skip to Main Content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

Erasmus Data Service Centre (EDSC) manuals

Overview of manuals provided by the EDSC

Replace all formulas in Excel

by Arjan De Wit on 2022-01-31T08:47:00+01:00 | Comments

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

 


 Add a Comment

0 Comments.

  Recent Posts



Finding Tobin’s Q using Eikon Datastream ‘Expressions’
Replace all formulas in Excel
Once you are finished downloading you might end up with a lot of sheets containing formulas. If you need to replace your formulas by values in all sheets at once you can use this macro.
Bloomberg doesn't export to Excel
Sometimes when you try to export to Excel nothing happens. This blogpost might help you to solve this issue.

  Subscribe



Enter your e-mail address to receive notifications of new posts by e-mail.


  Archive



  Return to Blog
This post is closed for further discussion.