How to Copy Multiple Sheets Using Excel VBA

Learn how to effortlessly copy multiple sheets without formulas or macros in Excel using VBA. Discover a step-by-step guide tailored for beginners!
---

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: copy more than one sheets using VBA macro

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Copy Multiple Sheets Using Excel VBA

If you're diving into the world of Excel VBA, you may encounter the need to create a new workbook that contains only values from certain sheets, without any formulas or macros. This task can seem daunting at first, especially for beginners. However, it's a common requirement for many Excel users wanting to share data without exposing sensitive formulas or code.

In this post, we'll walk you through the process of copying multiple sheets using VBA, ensuring that only the data you want is included in the new workbook. So, let’s get started!

The Problem: Copying Multiple Sheets Without Formulas

You have an existing workbook, and you want to create a new workbook that contains only the values from specific sheets, excluding any formulas or macros. This could be for sharing purposes, data analysis, or simply to declutter your workbooks.

The Solution: Step-by-Step Guide

1. Setting Up Your Environment

Before we jump into the code, ensure that you have the following setup:

A workbook open with the sheets you want to copy.

A basic understanding of how to access the VBA editor (press ALT + F11 in Excel).

2. The VBA Code Explained

Here’s a modified version of the code that will help you achieve your goal. This code creates a new workbook with specified sheets copied over as values only:

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

3. Code Breakdown

Here's a short explanation of what the code does:

Specify the Sheets to Keep: Change the SheetsToKeep variable to include the names of sheets you wish to retain in the new workbook.

Create a Copy of the Workbook: The code makes a duplicate of your current workbook, allowing you to manipulate it without affecting the original.

Loop Through Each Worksheet: It goes through each worksheet, checking if it matches the names in your SheetsToKeep list. If a sheet is not on the list, it gets deleted.

Paste Values and Number Formats: For each sheet you keep, the code copies its values (without formulas) and preserves the number formats.

Save the New Workbook: Finally, it saves the new workbook in an .xlsx format and deletes the original copied workbook that had macros.

4. Important Considerations

Overwrite Warning: The code will overwrite an existing file with the same name at the specified location. Be cautious if you have important data there.

Screen Updates & Alerts: The script suppresses screen updates and alerts while running. If an error occurs, it may leave alerts turned off until you restart Excel, so remember to restore them if needed.

Sheet Order Matters: The code processes sheets in the order they appear. If some sheets refer to data in sheets that will be deleted, you may need to adjust the loop structure.

Conclusion

Now you have a handy VBA macro that allows you to copy multiple sheets from an existing workbook into a new one without formulas or macros. This simple yet effective solution can greatly enhance your data management and sharing capabilities in Excel.

Feel free to modify the list of sheets to fit your specific needs, and happy coding! If you have any questions or would like further clarification, please leave a comment below.