Скачать или смотреть How to Save Multiple Sheets with VBA Codes by a Macro in Excel

Discover a step-by-step guide on how to save multiple sheets using VBA in Excel, ensuring each sheet is properly saved in a single new workbook without overwriting data.
---
This video is based on the question https://stackoverflow.com/q/76414480/ asked by the user 'Himanshu TOMAR' ( https://stackoverflow.com/u/21867823/ ) and on the answer https://stackoverflow.com/a/76414607/ provided by the user 'Horaciux' ( https://stackoverflow.com/u/2994412/ ) 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: How to save multiple sheets with VBA codes by a Macro

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

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Save Multiple Sheets with VBA Codes by a Macro in Excel

Working with Excel can sometimes feel overwhelming, especially when dealing with multiple worksheets that you want to save as separate files. Fortunately, with the power of VBA (Visual Basic for Applications), you can automate this process effectively.

In this guide, we’ll explore how to save multiple sheets from your workbook into a new file using a macro, providing a practical example to illustrate the solution.

The Problem: Saving Multiple Sheets in Excel

A common challenge arises when you want to save the filtered data from several sheets within a workbook. In many cases, users find that they can only access and save data from the last sheet of the workbook, resulting in unfiltered data being stored incorrectly.

The issue exists because the macro is designed to save each sheet individually but ends up overwriting the previously saved sheet due to using the same filename throughout the process.

Example of Initial Code

Here’s an excerpt from the initial code that the user was trying to implement:

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

The Solution: Enhancing the Macro

To effectively save all selected sheets into a single new workbook, we need to refine the original code. Here’s how to do it:

Step 1: Create a New Workbook

Instead of creating a new workbook for each sheet, we will create one workbook at the very beginning of the macro.

Step 2: Loop Through Specified Sheets

We will loop through each specified sheet, copy the filtered data into the new workbook, and ensure the unique naming convention prevents any overwriting.

Step 3: Finalize and Save the New Workbook

After copying all the sheets, we will save the newly created workbook once and close it to keep all changes.

Refined VBA Code

Here’s the updated and optimized code:

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

Conclusion

By following the above steps and utilizing the updated VBA code, you can effectively save multiple sheets from your Excel workbook into a single new file without losing any data. This method streamlines your workflow, particularly when handling large datasets.

If you have any questions or need further clarification on how to implement this solution, feel free to reach out in the comments below!