How to Copy Sheets to a New Workbook in VBA: Preserve Formulas and Values

Learn how to copy multiple sheets in Excel using VBA while preserving formulas in one specific sheet, and converting others to values.
---
This video is based on the question https://stackoverflow.com/q/67641390/ asked by the user 'achtoju' ( https://stackoverflow.com/u/15983051/ ) and on the answer https://stackoverflow.com/a/67641791/ provided by the user 'BigBen' ( https://stackoverflow.com/u/9245853/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA Copy sheets to new workbook - with/without formulas

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Copy Sheets to a New Workbook in VBA: Preserve Formulas and Values

Copying sheets from one workbook to another in Excel can often lead to an unexpected outcome, especially when it comes to formulas. If you're using Visual Basic for Applications (VBA) for automating this process, you might encounter issues where certain sheets lose their formulas upon copying. In this guide, we'll address a common problem faced by users who want to copy multiple sheets while maintaining one sheet's formulas. Let’s break down the solution step by step.

The Problem: Sheets Losing Formulas

When working with Excel and VBA, you might find yourself needing to copy multiple sheets to a new workbook. However, if one of those sheets contains formulas, you might notice that they get converted to static values during the copy operation. Here’s a scenario you might resonate with:

You have several sheets named “STRUCTURE,” “2,” “3,” and “1.”

You want to copy these sheets to a new workbook.

You need to keep the formulas in sheet “1” intact, while converting the others into values.

The Code That Causes the Issue

In the example code below, the part responsible for converting the formulas into values looks like this:

[[See Video to Reveal this Text or Code Snippet]]

This line essentially resets the cells to their values, eliminating any formulas. Now, let’s dive into how you can adjust your code to preserve the formulas for the targeted sheet.

The Solution: Two Approaches to Copying Sheets

You have two main options to ensure that one specific sheet retains its formulas while others are copied as linked values.

Option 1: Skipping the Worksheet Named "1"

The simplest approach is to check the name of each worksheet and skip the conversion for “1.” Here’s how to modify your code:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

This code loops through each worksheet in the active workbook.

It checks if the name is not “1,” and if true, it proceeds to convert the used range to values for that worksheet only.

Option 2: Processing Only Specific Worksheets

If you wish to target specific sheets directly, you can create an array to define which sheets need value conversion. This method reduces the overhead and is clearer.

Here’s what the adjusted code looks like:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

This approach explicitly defines the sheets you want to process as values.

It loops only over the sheets specified in the sheetNamesToProcess array, thus preserving the formulas in sheet “1” seamlessly.

Conclusion

By implementing either of the two methods detailed above, you can efficiently copy sheets to a new workbook while preserving the formulas in your designated sheet. Whether you choose to skip the specific worksheet or process only the sheets you want to convert, both approaches will help you maintain your desired data integrity.

Feel free to adapt and experiment with these code snippets in your VBA projects for Excel. Happy coding!

Видео How to Copy Sheets to a New Workbook in VBA: Preserve Formulas and Values канала vlogize