How to Copy an Excel Worksheet as Values Only Using VBA
Question details
The user needs to duplicate a worksheet and remove all its formulas, keeping only the static values, either on desktop Excel using VBA or on the web using Office Scripts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the process of creating a static copy of a formula-heavy worksheet for reporting, archiving, or sharing without exposing the underlying calculations.
- Observed behavior
- A new worksheet is created containing the exact same layout and data as the original, but all formulas are permanently converted to their calculated values.
Ensure you have saved a backup of your workbook, as running a macro typically clears your undo history. Also, verify that the Developer tab is enabled to access the VBA Editor.
Use a VBA Macro in Desktop Excel
Automate copying the active sheet and converting all formulas to values using a VBA macro.
This method is ideal for desktop versions of Excel. By saving the macro to your Personal Macro Workbook, you can run this automation on any open workbook with a single click or keyboard shortcut.
Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.
Click on Insert in the top menu, then select Module to create a blank script window.
Paste the macro code that utilizes the ws.Copy After:=ws and wt.UsedRange.Value = wt.UsedRange.Value commands to duplicate the sheet and overwrite formulas.
Close the VBA editor. Go to the Developer tab, click on Macros, select your new CopySheetValues macro, and click Run.
Use Office Scripts for Excel on the Web
For cloud users, Office Scripts provides a modern, TypeScript-based way to automate sheet duplication and value pasting.
Run Macros to Convert Formulas to Values in WPS Spreadsheet
WPS Office provides robust VBA support, allowing you to run macros to duplicate sheets and convert formulas to values seamlessly. It is a highly efficient alternative for managing complex spreadsheet tasks.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the formulas you wish to convert.
- 2. Access Developer Tools: Navigate to the Tools tab on the ribbon and click on 'Developer' to access the macro features.
- 3. Insert the Macro Code: Click 'Macros' or open the VBA Editor, insert a new module, and paste your formula-to-values macro code.
- 4. Execute the Automation: Save the module and click 'Run'. WPS will instantly create a duplicated worksheet containing only the static values.
Frequently Asked Questions
Can I undo a macro that converts formulas to values?
No, actions performed by a VBA macro usually clear the Excel undo stack. It is strongly recommended to save a backup of your workbook before running any script that overwrites formulas.
How do I make the VBA macro available in all my workbooks?
You can store the macro in your Personal Macro Workbook (Personal.xlsb). This is a hidden workbook that opens automatically in the background every time you start the application, making its macros accessible globally.
How do I assign this macro to a shortcut or button?
You can assign the macro to a button by right-clicking the Quick Access Toolbar, selecting 'Customize Quick Access Toolbar', choosing 'Macros' from the drop-down list, and adding your macro to the toolbar.
Why should I use Office Scripts instead of VBA?
Office Scripts are designed specifically for Excel on the web and cloud-based workflows. Unlike VBA, which only runs on desktop applications, Office Scripts can be triggered remotely via Power Automate and run across different platforms.
