VBA & Macro Problems

How to Extract Cells from Multiple Excel Workbooks Using a VBA Macro

WPS Content Manager Sep 28, 2026 870 views

Question details

The user needs to extract data from specific cells (E4, G8, D31) across approximately 100 Excel workbooks that share the same template and consolidate the results into a single summary worksheet.

How to Extract Specific Cells from Multiple Excel Workbooks via VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from multiple identically formatted Excel workbooks into one master sheet without opening each file manually.
Observed behavior: Instead of opening 100 files manually to copy and paste specific cells, a programmatic solution is required to automate the extraction and consolidation process.

Before you start

Ensure all target Excel workbooks are stored in a single folder and verify the exact worksheet names and cell references you need to extract data from. Also, confirm that macros are enabled in your spreadsheet settings.

Solution 1Recommended

Create a VBA Macro to Loop Through and Extract Data

Use a VBA script to automate opening each workbook, copying the specific cells, pasting them into a summary sheet, and closing the source files.

This macro will iterate through all `.xlsx` files in a specified directory. It opens each file, extracts the values from cells E4, G8, and D31, writes them into consecutive rows on your active workbook, and closes the source file without saving changes to speed up the process.

Open the VBA Editor

Open your master workbook where you want to consolidate the data and press ALT + F11 to open the VBA Editor.

Insert a New Module

Navigate to the 'Insert' menu at the top of the VBA Editor and click 'Module' to create a blank script window.

Write the Extraction Code

Write a VBA script using the Dir() function to loop through files in your folder, Workbooks.Open() to access each file, and assign the values of range E4, G8, and D31 to the next empty row in your master sheet.

Define the Folder Path

Update the folder path variable in your script to match the exact directory containing your 100+ workbooks (for example, "C:\Users\Data\Workbooks\").

Execute the Macro

Press F5 or click the 'Run' button on the toolbar to execute the macro. Check your summary sheet to ensure all data has been accurately populated.

Create a VBA Macro to Loop Through and Extract Data

Check File Extensions: If your source files are saved as .xls or .xlsm, remember to update the file extension in the Dir() function of your VBA code so the macro can locate them.

Automate Data with WPS Office

Consolidate Excel Workbooks with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, allowing you to seamlessly run your scripts to extract and consolidate data from hundreds of workbooks. It provides a familiar developer interface to write, edit, and execute your macros efficiently.

1. Launch WPS Spreadsheet: Open WPS Spreadsheet and navigate to the 'Developer' tab on the top ribbon.
2. Open the VBA Editor: Click on the 'Visual Basic' icon or simply press ALT + F11 on your keyboard.
3. Insert and Customize the Macro: Go to Insert > Module, paste your workbook extraction script, and adjust the target folder path to point to your files.
4. Run the Script: Click the Run button to instantly extract data from your 100+ files and compile them into the active WPS worksheet.

Fully compatible with Microsoft Excel VBA scripts and macrosLightweight application that handles large datasets and loops smoothlyFamiliar developer tab and VBA editor interfaceSupports opening and saving in standard Microsoft Excel formats like .xlsx and .xlsm

microsoft office alternative - wps office

Frequently Asked Questions

How do I enable the Developer tab to run VBA macros?

Go to the main settings menu or File > Options, navigate to 'Customize Ribbon', and check the box next to 'Developer' to display the tab on your main interface.

Can this macro extract data from specific worksheets rather than the active one?

Yes. You can modify the VBA code to reference a specific sheet by name, such as Sheets("Template").Range("E4"), instead of relying on the active or first sheet.

What if some of the target workbooks are in CSV format?

You need to adjust the VBA Dir() function pattern from "*.xlsx" to "*.csv" and ensure the code accounts for CSV formatting, though the folder looping logic remains identical.

Why does my macro run slowly when opening 100 workbooks?

Opening and closing multiple files consumes system resources and triggers screen updates. You can dramatically speed up the macro by adding Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual at the beginning of your script, turning them back on at the end.

Excel & Data Problems