Скачать или смотреть Efficiently Copy Data from Multiple Sheets to a New Excel Sheet

Discover how to efficiently copy data from multiple worksheets in Excel using VBA without manual effort, creating a consolidated view of your data.
---
This video is based on the question https://stackoverflow.com/q/72615414/ asked by the user 'Thoma55' ( https://stackoverflow.com/u/19336341/ ) and on the answer https://stackoverflow.com/a/72616601/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) 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: Copying the data from two columns from every sheet into a new sheet

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 Efficiently Copy Data from Multiple Sheets into a New Excel Sheet

If you work with Excel, you may find yourself in a situation where you have multiple worksheets in a workbook, each containing critical data in specific columns. One common challenge is consolidating columns from those sheets ("H" and "I", for example) into a single new sheet, while excluding certain worksheets like "Calc" and "Settings". This guide will show you how to do just that, using VBA to streamline the process.

The Challenge

Imagine you have the following setup in your workbook:

Sheet1: Data

Sheet2: Calc

Sheet3: Settings

Sheet4: Append1

Sheet5: Append2

And so forth...

Your goal is to consolidate columns "H" and "I" from every sheet except for "Calc" and "Settings" into a new sheet, named "Cons_Sheet". While you could try to use formulas, this becomes cumbersome and error-prone with larger datasets. Instead, we'll utilize VBA to automate this task.

The Solution

With VBA, we can quickly gather the data from the desired sheets and place it into a new worksheet efficiently. Below is the detailed breakdown of the VBA code that achieves this:

Step-by-Step Breakdown of the VBA Code

Initiate Variables:

Start by defining your workbook and worksheet variables, and arrays for storing data.

Loop Through Worksheets:

Use a loop to go through each worksheet in the workbook. For each sheet, check if the name is not "Calc" or "Settings".

Copy Data from Columns:

For each valid worksheet, find the last row of data in column "H" and copy values from columns "H" and "I".

Store Copied Data:

Utilize an array to compile the data as you loop through, avoiding the need to use the clipboard or any manual copying.

Create or Update Consolidated Sheet:

After collecting all data, create a new sheet named "Cons_Sheet" or clear it if it already exists.

Output the Data:

Finally, paste the collected array data into the new sheet.

The Complete VBA Code

Here’s the complete code you need to implement:

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

Conclusion

Using this VBA code, you can easily consolidate columns from multiple sheets into a new sheet without the hassle of manual work. This saves time and reduces the risk of error when dealing with large datasets. Give it a try and enhance your Excel skills!

Now you have everything you need to automate this process efficiently. Happy coding!