Excel VBA: Copy and Paste Values Across a Worksheet

To replace formulas with their current results across a worksheet in Excel for Microsoft 365 on Windows, run a VBA macro on that sheet’s used range: set a Range variable to ActiveSheet.UsedRange, then assign its values back to itself. This keeps cell formatting while converting formulas to fixed values; save a copy first, because a macro cannot be undone with Ctrl+Z.

What copying and pasting values does

Excel formulas are instructions that calculate results from other cells. For example, a cell containing a formula can display 125 while still depending on the cells used in that formula. Pasting values replaces that formula with the displayed result, so the cell contains a fixed number instead. If the formula displayed text or a date, the stored result is text or a date serial value, respectively.

That can be useful before sharing a report, archiving a snapshot, removing links to another workbook, or sending a sheet to someone who needs the results but not the calculations. It is a one-way change for the affected formulas unless you have a backup or can reconstruct them.

Values-only does not mean a complete visual or structural copy. The method below changes cell contents while leaving existing formatting in place. It does not remove borders, fills, number formats, or column widths. Nor does it automatically remove data validation, comments, hyperlinks, conditional formatting, or shapes. Those are separate worksheet features.

Use VBA to convert the used range

For most worksheets, the shortest solution is to assign the range’s values back to the same range. This avoids the clipboard and is a direct way to convert formulas in the range to their calculated results. The macro acts on the active worksheet, so select the intended sheet before running it.

Sub ConvertUsedRangeToValues()
    Dim ws As Worksheet
    Dim rng As Range

    ' Work on the currently active worksheet.
    Set ws = ActiveSheet
    Set rng = ws.UsedRange

    ' Replace formulas with their current results.
    rng.Value = rng.Value
End Sub

In this code, UsedRange means the area Excel regards as used, rather than every cell in the worksheet grid. A worksheet can contain more than a billion possible cell positions, so writing to every cell is usually unnecessary and can be very slow. The used range normally includes the cells containing data or formulas, but Excel may also treat cells with formatting as used.

Assigning rng.Value to itself preserves the current results, not the formula definitions. It does not copy formatting from another range because the source and destination are the same cells. If formulas have not recalculated, the values reflect the results currently held by Excel; check calculation settings and refresh the workbook before converting when up-to-date results matter.

Use VBA Paste Special when you want that operation

If you specifically want a VBA equivalent of Copy and Paste Special > Values, use the clipboard version below. It copies the used range and pastes values back over the same cells. The result is generally the same as the direct assignment, but this version uses Excel’s copy-and-paste mechanism.

Sub PasteValuesOverUsedRange()
    Dim ws As Worksheet
    Dim rng As Range

    ' Work on the currently active worksheet.
    Set ws = ActiveSheet
    Set rng = ws.UsedRange

    ' Copy the range, then paste only values in place.
    rng.Copy
    rng.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Both examples affect only the active worksheet. Neither loops through every worksheet in the workbook. To process another sheet, activate it first, or adapt the macro to refer to a named worksheet. Avoid running a workbook-wide version without checking its scope: converting formulas on every sheet can remove calculations you intended to keep.

How to add and run the macro

Before running VBA, save a separate copy of the workbook. If you need to keep the macro in the file, save it as an Excel Macro-Enabled Workbook with the .xlsm extension; saving a VBA project in a standard .xlsx file removes the macros.

In desktop Excel, open the workbook and select the worksheet you want to change.
Open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select the Developer check box.
In the editor, choose Insert > Module, paste one of the macros into the module, and close or switch away from the editor.
Run it from Developer > Macros: select the macro name and choose Run. You can also place the cursor inside the procedure in the Visual Basic Editor and press F5.

Excel may show a security warning or block macros according to the workbook’s origin and your organization’s policy. Enable macros only when you trust the file and its source. Do not lower macro security for an unknown workbook just to run this operation.

Excel for Mac supports VBA, although ribbon layouts and keyboard behavior can differ from Windows. Use the Visual Basic Editor and Macros commands in the Mac desktop app; the exact shortcut for opening the editor can depend on the keyboard and function-key settings. Excel for the web does not create or run VBA macros, so open the workbook in the desktop application to use this code.

Choose the right range and scope

UsedRange is convenient, but it is not always identical to the cells you consider meaningful. For example, someone may have formatted a cell far below the actual table. Excel can then report a larger used range than expected. A large used range can make a macro take longer, even though formatting-only cells contain no formulas to convert.

Before running the macro, inspect the worksheet’s apparent boundaries and consider whether it includes distant formatted cells. If the sheet contains a clearly defined table or block, you can instead change the macro so rng refers to that specific range. For example, if the data occupies B2:H500, set the range to that block rather than using the entire UsedRange. That limits the change and reduces the chance of converting unrelated formulas elsewhere on the sheet.

Keep in mind that the range must cover every formula you want to freeze. A worksheet can have formulas outside its main table, such as a note, a summary calculation, or a check in a distant column. Review those areas before choosing a smaller range.

Use UsedRange when you want the broad area Excel identifies as used on one sheet.
Use a specific range when the target is a known table or report block.
Use a workbook-wide procedure only when you explicitly intend to replace formulas on multiple sheets.

What changes, and what stays the same

After conversion, ordinary formulas in the target range become their current results. A formula such as =SUM(B2:B10) will no longer update when a value in B2:B10 changes. Cell formatting remains, so a numeric result can continue to display as currency or a date if that number format was already applied.

Relative references, named formulas, and links used by converted formulas are no longer needed by those cells, but other formulas elsewhere in the workbook may still depend on the converted cells. Those dependent formulas continue to calculate from the fixed values. A formula returning an empty string may become a text value that looks blank; it is not necessarily the same as a truly empty cell.

Some worksheet structures need extra care. Dynamic-array formulas can spill results into neighboring cells, and Excel may prevent changes to part of a spill range. Legacy array formulas and merged cells can also restrict edits to only part of a range. If the macro reports an error or stops partway through, inspect those features and test a smaller, ordinary range in the backup before trying again.

Common problems and safe handling

The wrong sheet changes: The sample macro uses ActiveSheet. Click the intended worksheet tab before running it, then verify the selected sheet name in advance.
Unexpected cells are included: Formatting far from the data can expand UsedRange. Check the scope or set a precise range before conversion.
The result is out of date: The macro writes the results Excel currently has. Recalculate the workbook first if calculation is set to manual or if external data has not refreshed.
The macro errors on a special range: Spill formulas, array formulas, or merged cells may not behave like ordinary individual cells. Restore the backup and isolate the affected range rather than repeatedly running the macro on the full sheet.
You need to reverse the change: Ctrl+Z on Windows and Command+Z on Mac do not undo a VBA macro. Restore the backup or recover an earlier workbook version.

For an extra safeguard, make a duplicate worksheet before converting a complicated report. Right-click its tab and choose Move or Copy, then select Create a copy. A duplicate can help with review, but it is not a substitute for saving a separate workbook copy: macros can affect more than expected, and a backup file is easier to restore if the workbook is damaged or the wrong sheet was changed.

Frequently Asked Questions

How do I paste values only in VBA? Use Range.PasteSpecial Paste:=xlPasteValues after copying a range, or assign the range’s .Value property to itself to replace formulas with their current values without using the clipboard.

Does this copy the whole worksheet, including formatting? The examples convert contents in the used range on one worksheet; they do not copy a worksheet to another location. Existing formatting stays in place, but the macros do not copy or remove the sheet’s other features.

Will the macro change every sheet in the workbook? No. The sample procedures use ActiveSheet, so they change the selected sheet only. You would need to write a separate loop through worksheets to change multiple sheets, and should do so only if that is intended.

Can I undo a VBA values-only macro? Not with Excel’s usual Undo command. Save a separate workbook copy before running it, because the macro replaces formulas and can’t be reversed with Ctrl+Z or Command+Z.

Can I run this macro in Excel for the web? No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the VBA Editor and run the procedure.

When a manual method or another tool is better

For a one-time conversion of a modest range, a macro may be unnecessary. Select the cells, copy them, then use Home > Paste > Paste Special > Values to paste the results over the originals. This also replaces formulas, so the same backup warning applies. The exact menu appearance can vary with window size, but Paste Special is available in desktop Excel.

Use VBA when the same conversion is part of a repeatable process, the target range is large, or you need a consistent one-click routine. If your goal is to create a static snapshot while preserving the working formulas, copy the worksheet or range to a separate destination and paste values there instead of overwriting the source. That approach keeps the original calculations available for later updates.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.