Replace Formulas with Values (For The Entire Workbook)

[hematinsite]

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]

How about ...

Sub hematinsite()

    Dim n As Name, r As Range
    For Each n In ThisWorkbook.Names
        If Right(n.Name, 11) = "!Print_Area" Then
            Set r = Range(Replace(n.RefersTo, "=", ""))
            If r.Parent.Visible = xlSheetVisible Then
                r.Copy
                r.PasteSpecial (xlPasteValues)
            End If
        End If
    Next
End Sub

 

[hematinsite]

no anything do this vba . i checked and run it but do anything

 

[Domenic]

Your code referred to the active workbook, whereas @GWteB 's code refers to the workbook running the macro. If you want the code to refer to the active workbook, replace...

For Each n In ThisWorkbook.Names

with

For Each n In ActiveWorkbook.Names

Hope this helps!

 

[mohadin]

Hi what about

Sub test()
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
a = Cells(1, 1).Resize(RCount, CCount).Value
Cells(1, 1).Resize(RCount, CCount) = a
End If
Next i
End Sub

 

[hematinsite]

Your code referred to the active workbook, whereas @GWteB 's code refers to the workbook running the macro. If you want the code to refer to the active workbook, replace...

For Each n In ThisWorkbook.Names

with

For Each n In ActiveWorkbook.Names

Hope this helps!

i did it , but anything to do

 

[Domenic]

Do you in fact have print areas set up using the method Ribbon >> Page Layout tab >> Page Setup group >> Print Area >> Set Print Area?

Can you confirm whether you want the code to work on the active workbook or the workbook running the code?

 

[Domenic]

Maybe it's an issue to do with language. When you look at your Name Manager, are the names for the print areas named Print_Area? If not, how are they named?

 

[GWteB]

@hematinsite, my code assumes that each of your worksheets, as you indicated in your post #1, has a delimited Print_Area, as shown in the accompanying image.
If not then my code has no effect at all.

 

[hematinsite]

Hi what about

Sub test()
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
a = Cells(1, 1).Resize(RCount, CCount).Value
Cells(1, 1).Resize(RCount, CCount) = a
End If
Next i
End Sub

this work very fast and perfectly . thx dear