hematinsite
New Member
- Joined
- Nov 3, 2020
- Messages
- 14
- Office Version
- 2019
- Platform
- 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
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