Excel tutorials

Loop Through Multiple Sheets And Save All Without Formulas

If you are reading this Excel tutorial, I believe that you are a user of Excel that has a need to save the workbook or save multiple worksheets as values but the saved workbook does not contain formulas. Faced with the same need recently, I decided to collect a variety of variants of VBA macros that work the same thing in different ways. This Excel tutorial contains a collection of VBA macros of various authors and all merit belong to authors, ie creators of VBA macros (source: answers.microsoft.com, rondebruin.nl, mrexcel.com, excelforum.com, extendoffice.com, internet network etc).

As you know the program code for VBA macros or the procedure has the beginning and the end, which between these two lines of code contains other parts of the VBA code for a particular action. All VBA macros displayed on this web site refer to an active workbook that contains multiple worksheets or an active worksheet. If you are looking for a VBA macro that saves in multiple worksheets located in multiple folders and subfolders then look at the previous tutorial.

- Save all worksheets as values (text) in multiple workbooks in all folders and subfolders

Each VBA macro has an event in which a VBA code is implemented for a particular action. The text below shows the basics of a VBA procedure.
ATTENTION! Use all VBA macros on this website at your own risk. I advise you, you should work on a copy of your workbook. After starting VBA macros there is no return back, (UNDO command does not work).

Record VBA macros for a particular action in Excel

When it comes to working with VBA macros in Excel, let's start from the beginning. For each action we often repeat, we can record a VBA macro. In this case I will show you how to record a VBA macro that will do the following:

  1. On an active worksheet that contains a lot of formulas
  2. Select all the cells
  3. Copy command - Copy all selected cells
  4. Paste command - paste the copied cells as values (no formulas)

You can also watch my VIDEO on YouTube. So this is an example of recording a VBA macro using the command "Record Macro" in Excel.

Sub Macro1()
Cells.Select 'the user used the Ctrl+A
Selection.Copy 'the user used the Ctrl+C
'the user used the Paste button -> Paste Values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select 'the user clicked on A1 cell
End Sub

Save the formula as a value in Excel

How to quickly and easily replace formula with result

If you work with cell ranges and you have a lot of formulas on the worksheet, and at a certain point you want to replace the formula with the result, do the following:

The first way

  1. Right-click on the cell choose 'Copy'
  2. Right-click on the cell and choose 'Paste Special'
  3. Highlight the radio button 'Values'
  4. Click the OK button

The second way

  1. Select the cell in which the formula is and contains the result obtained by the formula
  2. Press the 'F2' key
  3. Press the 'F9' key
  4. Press the Enter key

The Third way

  1. Select the cell/cell range that contains formulas and contain the result obtained by formulas
  2. Press the combination of the 'CTRL + C'
  3. Press the combination of the 'SHIFT + F10 + V' keys (Hold Shift key)
  4. Press the Enter key to deselect the selection

The fourth way

  1. Select the cell / cell range that contains formulas and contain the result obtained by formulas
  2. Press the combination of the 'CTRL + C'
  3. Press the combination of the 'ALT + E + S + V' keys (hold the Alt key)
  4. Click the OK button

The Fifth Way

  1. Press the combination of the 'CTRL + A'
  2. Press the combination of the 'CTRL + C'
  3. Click on Paste -> Paste Special -> Value

Now instead of formula, cell or range contains values (in text form)

Example of basic VBA procedures

In principle, the VBA macros that are displayed on this website are copied to the standard Module in your Excel VBE.

Option Explicit
Sub SaveAsValues() 'title of subprocedure
Declared variables
'option 'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
'''''''''''''''''''''''''''''''''''
'--YOUR CODE HERE--
'''''''''''''''''''''''''''''''''''
Application.CutCopyMode = False 'disable selection
'option 'Reset Macro Optimization Settings
Application.EnableEvents = True

Application.ScreenUpdating = True
MsgBox "Process Completed!" 'option Message Box when process are completed
End Sub 'end of sub procedure

Convert Formulas to Values in all Sheet Tabs in Workbook

A short and simple VBA macro to convert the formula to the values on all worksheets in the open workbook is this below. If you want to embed one of these three VBA macro into another VBA procedure, then notice that there is no command line code that would save changes to the workbook. So, you should add the save code.

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

or below this VBA macro

Sub SaveAllFormulasAsValues()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next sh
End Sub

or below this VBA macro

Sub SaveAllFormulasAsTextOrValues()
Dim w As Long
For w = 1 To Sheets.Count
Sheets(w).UsedRange = Sheets(w).UsedRange.Value
Next w
End Sub

Save the active workbook as a values without formula - make a copy in XLSX format

Example 1

This VBA macro below, saves the active workbook of * .XLSM format as a copy in * .XLSX format. Note the code lines that are intended to add a date, ie year and month, to the name of the saved workbook. The original active book remains unchanged. There will be no VBA modules as well as VBA macros in the saved copy if it has them in other sheet modules.

Option Explicit
Sub SaveAsValuesAllSheetsAndSaveWbkAsXLSX()
'author Jeeped
'answers.microsoft.com
'modified by www.ic-ims.com
Dim w As Long
Application.ScreenUpdating = False 'option 'Optimize Macro Speed
Application.EnableEvents = False 'option 'Optimize Macro Speed
For w = 1 To Sheets.Count
Sheets(w).UsedRange = Sheets(w).UsedRange.Value
Next w
Application.DisplayAlerts = False
''save with date year and month in filename
' ThisWorkbook.SaveAs _
' ThisWorkbook.Path & Chr(92) & _
' Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, Chr(46)) - 1) & Format(Date, "_yyyy-mm"), _
' xlOpenXMLWorkbook
'save without date in filename
ThisWorkbook.SaveAs _
ThisWorkbook.Path & Chr(92) & _
Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, Chr(46)) - 1), _
xlOpenXMLWorkbook
Application.EnableEvents = True 'option 'Reset Macro Optimization Settings
Application.ScreenUpdating = True 'option 'Reset Macro Optimization Settings
MsgBox "Process Completed!" 'option Message Box when process are completed
End Sub

Example 2

This VBA macro below goes through all the worksheets of an active workbook and saves a workbook without formulas (as a value). After you start the macro, you will see the 'SaveAs' dialog box, you need to set a new workbook name and choose a folder to save the workbook.

Sub SaveTheWorkbookAsValuesOnly()
'by Dave Hawley
'ozgrid.com
'modified by www.ic-ims.com
Dim wSheet As Worksheet
Application.ScreenUpdating = False 'option 'Optimize Macro Speed
Application.EnableEvents = False 'option 'Optimize Macro Speed
On Error Resume Next
For Each wSheet In Worksheets
With wSheet.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With
Next wSheet
Application.CutCopyMode = False
On Error GoTo 0
Application.EnableEvents = True 'option 'Reset Macro Optimization Settings
Application.ScreenUpdating = True 'option 'Reset Macro Optimization Settings
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Save all Worksheets as Values Without Formulas

Example 3

In this third example, it shows two ways we can go through all the worksheets and convert all the formulas into their results or values. Notice the difference in the part of the code that applies to all worksheets. For other VBA macros, visit its web site rondebruin.nl.

Sub SaveAllWorksheetAsValues()
'by rondebruin.nl
'modified www.ic-ims.com
Dim sh As Worksheet
Application.ScreenUpdating = False 'option 'Optimize Macro Speed
Application.EnableEvents = False 'option 'Optimize Macro Speed
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
Application.EnableEvents = True 'option 'Reset Macro Optimization Settings
Application.ScreenUpdating = True 'option 'Reset Macro Optimization Settings
End Sub

Exmple 4

This VBA macro saves all worksheets (within an active workbook) as a values.

Option Explicit
Sub SaveAsValuesOnly()
Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Worksheets
wsh.Cells.Copy
wsh.Cells.PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End Sub

Save all formulas as values for the defined cell range or areas on the active worksheet

In this part of the example for converting formula to value, VBA macros give the same result in different ways. All merit belong to authors (source: mrexcel.com)

Example 5

Sub ChangeOrConvertToValues()
'used range A:E on active sheet
With Range("A2", Cells(Rows.Count, "E").End(xlUp).Offset(-1)) 'not include last row -> -1
.Value = .Value
End With
End Sub

Example 6

Sub SaveAsValues1()
'non-adjacent ranges on active sheet
Dim Cl As Range
For Each Cl In Range("A2:B33,D2:E33")
Cl.Value = Cl.Value
Next Cl
End Sub

Example 7

Sub SaveAsValues2()
'non-adjacent ranges on active sheet
Dim c As Range
Dim rng As Range
Set rng = Range("A2:B33,D2:E33")
For Each c In rng.Cells
c = c.Value
Next
End Sub

Example 8

Sub SaveAsValues3()
'non-adjacent ranges on active sheet
Dim oneArea As Range
For Each oneArea In Range("A2:B33,D2:E33").Areas
oneArea.Value = oneArea.Value
Next oneArea
End Sub

Save all formulas as values if they start with a specific expression

In this example below, you can see the VBA macro that will go through all the worksheets in an active workbook and find some formulas that begin with a specific expression (in this case, it is the beginning of the formula "= helper!" Which contains 8 characters without a quotation) . When WBA make such a formula, the same will replace the results of the corresponding formula. This interesting VBA macro can be used if we only want to convert certain formulas to values. Here is shown the expression is the beginning of a formula that pulls data from the 'helper' worksheet.

Example 9

'Option Explicit
Sub ReplaceCertainFormulaOnMultipleTabsAsValues()
'author sheetspread
'source mrexcel.com
'published by www.ic-ims.com
Dim x As Integer
For x = 1 To Sheets.Count
For Each Cell In Sheets(x).UsedRange
If Left(Cell.Formula, 8) = "=helper!" Then 'the beginning of the formula
Cell.Value = Cell.Value
End If
Next Cell
Next x
End Sub

Transform all formulas into values on an active worksheet.

These two examples below represent VBA macros that transform formula into values, for a specific cell range on an active worksheet. In Example 10 the cell range is defined, but in Example 11 before the start of the VBA macro the user needs to select a specific cell range that contains the formulas (source: extendoffice.com)

Example 10

Sub TransformFormulasToValues()
'Converts formulas to values in the defined range
With Range("A1:A100")
.Value = .Value
End With
End Sub

Example 11

Sub TransformFormulasToValues2()
'Converts formulas to values in the selected range
With Selection
.Value = .Value
End With
End Sub

Replace a Formula With its Result and Protect the Worksheet

In an open workbook, save multiple worksheets as a values and protect them with a password

This VBA macro below, after saving all formulas as a values automatically locks/protects the worksheet with a password defined.

Sub ConvertFormulasToValuesAndLockSheets()
'published by www.ic-ims.com
Dim ws As Worksheet
Dim passw As String
'add option if you want 'Optimize Macro Speed
passw = "123" 'defined password
For Each ws In Worksheets
'ws.Unprotect Password:=passw 'option if password exist, unlock sheet
ws.UsedRange = ws.UsedRange.Value 'all cells on sheet
ws.Protect Password:=passw, UserInterfaceOnly:=True 'lock sheet
ws.EnableOutlining = True
Next ws 'next sheet
'add option if you want'Reset Macro Optimization Settings
End Sub

All the VBA macros that are shown on this web site are related to saving worksheets or workbooks without formulas, ie converting the formula into their results. On the internet, you can find different ways and different VBA macros. I hope that this collection of VBA macros will help you use Excel if you need to save your workbook / worksheets without formulas.