Скачать или смотреть Extracting a Range of Non-Contiguous Cells in Excel VBA

Discover how to extract non-contiguous cells from two sheets in Excel VBA with ease. Follow this simple guide to enhance your data handling skills.
---
This video is based on the question https://stackoverflow.com/q/70979790/ asked by the user 'Nandakishore' ( https://stackoverflow.com/u/15957944/ ) and on the answer https://stackoverflow.com/a/70980214/ provided by the user 'Tim Williams' ( https://stackoverflow.com/u/478884/ ) 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: Extracting a range of non-contiguous cells

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.
---
Extracting a Range of Non-Contiguous Cells in Excel VBA: A Step-by-Step Guide

Have you ever found yourself needing to extract data from multiple sheets in Excel, particularly when the data exists in non-contiguous cells? This can be a common challenge when working with different files, especially if you need to pull data from two unique sheets—let's say "summary1" and "extract1".

In this guide, we will address this specific problem and provide a solution using VBA (Visual Basic for Applications). By the end of this guide, you will be able to efficiently extract ranges of non-contiguous cells from your Excel files.

The Challenge

The problem arises when you want to collect data from various Excel files located in a specific folder. Each file contains either the "summary1" sheet or the "extract1" sheet, but not both.

With your existing code, you may have encountered an error when trying to extract data using both sheets. The good news is that we can modify the VBA code to find the correct sheet dynamically and extract the required data without the error.

Solution Overview

To effectively tackle the problem, we will:

Set up constants for the folder path and cell range.

Open each file in the specified directory.

Determine which sheet (either "summary1" or "extract1") exists in that file.

Extract the specified non-contiguous cells from the found sheet.

Store the values in a master sheet.

Step by Step Code Walkthrough

Here’s the revised VBA code that handles the extraction process:

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

Explanation of the Code

Constants: The PTH constant sets the target folder for your Excel files, and RNG defines which cell ranges to extract.

Opening Workbooks: The Do While loop iterates through each Excel file in the directory and opens it for processing.

Finding the Correct Sheet: The FindFirstSheet function checks if either "summary1" or "extract1" exists in the opened workbook. This is achieved through another loop that checks each sheet name.

Extracting and Storing Data: The specified non-contiguous cell ranges are extracted and pasted into the master sheet. Each value from the cells is copied into consecutive columns with the file name added at the last column.

Final Touches: Auto-fit the columns for better readability, turn back on display alerts, and notify the user once the data has been successfully compiled.

Conclusion

By using this updated VBA code, you can now efficiently extract non-contiguous cells from two unique sheets across your Excel files without running into errors. Engage with Excel VBA in a more powerful way and improve your productivity in data management tasks. If you have any questions or need further assistance, feel free to reach out!