VBA & Macro Problems

How to Copy Excel Cell Values Without Formulas Using VBA

Partner Editor Sep 28, 2026 870 views

Question details

The user needs to automate the process of copying calculated inventory values as static numbers without transferring the underlying formulas.

How to Copy Excel Cell Values Without Formulas Using VBA

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Preparing a supplier workbook that requires final numeric values instead of formulas for inventory data reporting.
Observed behavior: The current workflow relies on repetitive, manual Paste Special > Values operations to strip out formulas, reducing productivity and increasing the risk of missing cells.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code execution, and verify that the Developer tab is enabled in your software's ribbon settings.

Solution 1Recommended

Use VBA Value Property Assignment

The most efficient way to copy values without formulas in VBA is by directly equating the Value property of the target range to the source range.

By assigning the values directly through VBA rather than simulating copy-and-paste commands, you bypass the system clipboard entirely. This approach is cleaner, faster, and prevents potential clipboard interference when automating large inventory spreadsheets.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, right-click on your workbook's name in the Project Explorer panel on the left, select Insert, and then click Module.

Enter the VBA Code

Paste the following script into the blank module: Sub CopyValues() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim sourceRange As Range Dim targetRange As Range Set sourceSheet = ThisWorkbook.Sheets("Sheet3") Set targetSheet = ThisWorkbook.Sheets("Sheet1") Set sourceRange = sourceSheet.Range("A1:B10") Set targetRange = targetSheet.Range("A1:B10") targetRange.Value = sourceRange.Value End Sub

Customize Ranges and Sheet Names

Modify the sheet names (e.g., "Sheet3" and "Sheet1") to match your actual workbook structure. Update the ranges (e.g., "A1:B10") to target the specific inventory cells you wish to convert. Ensure the source and target ranges are identical in size.

Run the Macro

Save your work, close the VBA editor, and press Alt + F8 to open the Macro dialog. Select CopyValues and click Run to automatically transfer the data as static values.

Save as Macro-Enabled File: Be sure to save your workbook using the .xlsm extension (Excel Macro-Enabled Workbook) to ensure your new VBA code is preserved for future use.

Advance your data processing with WPS Spreadsheet

Automate Excel Tasks Seamlessly with WPS Office

WPS Spreadsheet fully supports VBA scripts, allowing you to easily automate repetitive tasks like copying cell values without formulas. It provides a lightweight, high-performance environment that accurately executes standard Excel macros.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx file containing the inventory data formulas.
2. Access the Macro Editor: Navigate to the Tools tab on the top ribbon and click on Macros or Visual Basic to launch the integrated WPS VBA environment.
3. Insert and Run the Code: Create a new module, input the direct value assignment script, and trigger it using the built-in Macro Manager to convert formulas to static values.

Full support for VBA scripts and .xlsm formats to automate complex operations.Highly compatible with Microsoft Excel formulas, functions, and formatting.Lightweight software architecture for faster loading and smoother processing of large datasets.Intuitive, tabbed user interface for seamless workflow migration.

microsoft office alternative - wps office

Frequently Asked Questions

Can I copy values from multiple cells at once using this VBA method?

Yes, you can copy a range containing multiple cells by adjusting the range references. Simply set your sourceRange to cover the whole area, for example, Set sourceRange = sourceSheet.Range("A1:D50"), and ensure your targetRange matches the exact same dimensions.

Why am I getting an error when assigning the values?

Errors usually occur if the targetRange and sourceRange are defined with different sizes, or if the worksheet names written in the VBA script do not exist in your workbook. Check your dimensions and spelling to ensure they match exactly.

Can I use PasteSpecial in VBA instead of the Value property?

Yes, you can alternatively use sourceRange.Copy followed by targetRange.PasteSpecial Paste:=xlPasteValues. However, using the direct .Value = .Value method is considered best practice because it executes faster and does not overwrite data saved in your clipboard.

How do I save my workbook after writing the VBA script?

Standard .xlsx files cannot store macros. You must go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown list to retain your code.

Excel & Data Problems