VBA & Macro Problems

VBA Macro to Copy an Excel Workbook and Convert All Sheets to Values

Steven Parker Oct 19, 2026 868 views

Question details

The user needs a VBA macro that can duplicate an entire Excel workbook and convert all formulas (like VLOOKUPs) into static values across every worksheet, while preserving the original workbook.

How to Use a VBA Macro to Copy an Excel Workbook and Convert All Sheets to Values

Product: Excel
Device & OS: not provided
Scenario: Sharing or archiving data sets that contain multiple sheets with heavy formulas, where copying individual worksheets manually is error-prone and tedious.
Observed behavior: Copying tabs into a new workbook manually often breaks existing named ranges, charts, and pivot tables. An automated macro is required to seamlessly clone the file and strip the formulas.

Before you start

Ensure that your original workbook is saved to a specific folder on your computer before running the macro, as the script relies on the active file's directory path to generate the new copied workbook.

Solution 1Recommended

Use a VBA Macro to Save a Copy and Convert All Sheets to Values

This recommended macro creates an exact clone of your active workbook, opens the clone, converts all formulas to static values across every worksheet, and saves it automatically. This prevents worksheet relationships and objects from breaking.

By duplicating the entire workbook at the file level before changing formulas to values, you bypass the errors typically caused by manually copying worksheets that contain complex named ranges, charts, or pivot tables.

Open the VBA Editor

Open your original workbook in Excel. Press the Alt + F11 keys on your keyboard simultaneously to launch the Microsoft Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, look at the Project Explorer pane on the left. Right-click on your workbook's name (e.g., VBAProject(YourFile.xlsx)), hover over 'Insert', and select 'Module'. A new blank window will appear.

Paste the Macro Code

Copy and paste the following code into the empty module window: Sub CopyWorkbookAsValues() Dim sourceWb As Workbook Dim copiedWb As Workbook Dim ws As Worksheet Dim baseName As String Dim extension As String Dim copyPath As String Set sourceWb = ActiveWorkbook If sourceWb.Path = "" Then MsgBox "Save the source workbook before running this macro.", vbExclamation Exit Sub End If baseName = Left(sourceWb.Name, InStrRev(sourceWb.Name, ".") - 1) extension = Mid(sourceWb.Name, InStrRev(sourceWb.Name, ".") + 1) copyPath = sourceWb.Path & Application.PathSeparator & baseName & "_Values." & extension sourceWb.SaveCopyAs copyPath Set copiedWb = Workbooks.Open(copyPath) For Each ws In copiedWb.Worksheets With ws.UsedRange .Value = .Value End With Next ws copiedWb.Save copiedWb.Close SaveChanges:=False MsgBox "The copied workbook was created successfully: " & copyPath, vbInformation End Sub

Run the Macro

Click anywhere inside the pasted code and press F5 on your keyboard, or click the green 'Run' triangle button in the toolbar. The macro will execute, duplicate the file in the same folder, convert the values, and display a success message.

Use a VBA Macro to Save a Copy and Convert All Sheets to Values

File Naming: The new file will automatically be saved in the same directory as your original file, with '_Values' appended to the original file name.

Solution 2

Convert Formulas to Values in the Current Workbook Only

If you prefer to manually copy the workbook file yourself through Windows File Explorer, you can use a simpler VBA script to convert all formulas to values on the currently open file.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust support for running complex VBA macros, making it incredibly easy to automate repetitive tasks like converting entire workbooks from formulas to values. With an intuitive interface, it serves as a highly efficient alternative for professional spreadsheet management.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet application, and load the file containing your formulas.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' to open the macro features.
3. Open the VBA Editor: Click the 'Visual Basic Editor' icon. This will launch the VBA environment exactly as you expect.
4. Insert and Run the Code: Click 'Insert' > 'Module', paste the workbook copying script, and click 'Run' to seamlessly convert your formulas to values.

Full compatibility with Microsoft Excel formats (.xlsx, .xlsm, .csv)Native support for executing VBA macros and modulesLightweight application that opens large data files rapidlyFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why is it better to copy the workbook instead of copying all worksheets to a new file?

Pasting all worksheets into a newly created blank workbook is error-prone. This method often breaks existing named ranges, chart data sources, and pivot table links. Copying the file at the system level (using SaveCopyAs) ensures all internal workbook structures remain completely intact before formulas are removed.

Can I undo the macro after it converts formulas to static values?

No. Running any VBA macro automatically clears your undo stack. This is why the recommended solution saves a distinct copy of your workbook (e.g., adding '_Values' to the file name) and performs the conversion on the copy, leaving your original formulas completely untouched.

Will this macro work if my Excel workbook contains hidden worksheets?

Yes, the macro iterates through every worksheet in the 'Worksheets' collection, whether they are visible or hidden, and will replace the formulas with values inside the 'UsedRange' of each sheet.

Why do I get a 'Save the source workbook before running' error message?

The VBA macro needs to know where to save the duplicate file. If you have created a new workbook but haven't saved it to your computer yet, the active file does not have a file path. Save the document to your hard drive first, then run the macro.

Excel & Data Problems