Replace Formulas with Values (For The Entire Workbook)

hematinsite

New Member
Joined
Nov 3, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
hi dear
in excel 2019 , Is it possible to have a macro that in my entire workbook in any cell in print area , if i use formula then convert to their value ? i have below vba code and work for small sheets but for big sheets is very heavy and need so time to execute and finish , i want to limit range in print area for convert formula to value in sheets and entire workbook.

Sub FormulasToValues_EntireWorkbook()
'This Macro will replace all formulas from an entire workbook
'with their respective values. It will just changes all
'formulas to values.

WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
RCount = ActiveCell.SpecialCells(xlLastCell).Row
CCount = ActiveCell.SpecialCells(xlLastCell).Column
For j = 1 To RCount
For k = 1 To CCount
Worksheets(WCount - i + 1).Cells(j, k) = Worksheets(WCount - i + 1).Cells(j, k).Value
Next k
Next j
End If
Next i

End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting us know.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,112,799
Messages
5,542,574
Members
410,560
Latest member
1ndependent
Top