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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
How about ...
VBA Code:
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

New Member
Joined
Nov 3, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
no anything do this vba . i checked and run it but do anything
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
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...

VBA Code:
For Each n In ThisWorkbook.Names

with

VBA Code:
For Each n In ActiveWorkbook.Names

Hope this helps!
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
603
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

hematinsite

New Member
Joined
Nov 3, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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...

VBA Code:
For Each n In ThisWorkbook.Names

with

VBA Code:
For Each n In ActiveWorkbook.Names

Hope this helps!
i did it , but anything to do
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
@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.

ScreenShot004.png
 

hematinsite

New Member
Joined
Nov 3, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,562
Members
410,560
Latest member
1ndependent
Top