Excel to replace formulas with values

Question

I have an Excel workbook (1) with around 9 sheets that is pulling in and manipulating data from a second workbook (2).

After pulling in the data from workbook (2) I need to be able to replace the formulas in workbook (1) to the resulting values that the formulas have produced, from here I will then save the workbook (1) with the results.

Is there a macro that can do this for me?

Answer 1

On your new workbook some basic code such as:

Sub Value()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next
End Sub