VBA & Macro Problems

How to Copy Rows from Multiple Excel Workbooks into One Worksheet using VBA

Steve K Sep 28, 2026 868 views

Question details

The user needs to automate the process of copying a specific range of rows (e.g., rows 2 to 13) from the first worksheet of multiple Excel files in a folder and appending them into a single master worksheet.

How to Copy Rows from Multiple Excel Workbooks into One Worksheet

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from numerous similarly structured source files into one central document for reporting or analysis.
Observed behavior: The user wants a VBA macro to loop through files using the Dir function, copy the specified range, paste it below existing headers, and safely close the source files without saving changes.

Before you start

Before running the macro, ensure all source workbooks are placed in a single, accessible folder, and always backup your master workbook in case the VBA script accidentally overwrites existing data.

Solution 1Recommended

Use a VBA Macro to Loop Through and Copy Data

Create a VBA script that utilizes the Dir function to sequentially open each file, extract the target range, and append it to the master sheet.

This method is highly efficient for consolidating dozens or hundreds of files. It relies on a loop to fetch the files, copy the target range (like A2:W13), and locate the next available blank row in the destination worksheet so that no data is overwritten.

Open the VBA Editor

Press Alt + F11 in your master workbook to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a blank script window.

Input the Consolidation Code

Write a VBA script using the `Dir` function to loop through your designated folder. Use `Workbooks.Open` to open each file, and specify `Range("A2:W13").Copy` on `Worksheets(1)` to grab the data.

Define the Paste Destination

In your code, find the next empty row in your master sheet using `Cells(Rows.Count, 1).End(xlUp).Row + 1` to paste the copied data, then use `ActiveWorkbook.Close False` to close the source file.

Run the Macro

Update the folder path string in your code to match the directory containing your source workbooks, then press F5 to execute the macro.

Use a VBA Macro to Loop Through and Copy Data

Verify File Paths: Ensure your folder path string ends with a backslash (e.g., 'C:\Users\Data\Folder\') so the Dir function can correctly identify the files.

Solution 2

Consolidate Workbooks Using Power Query

If you prefer a no-code approach, use Power Query to automatically combine data from all workbooks in a specified folder.

Automate with WPS Spreadsheets

Use WPS Office to Consolidate Your Excel Workbooks

WPS Spreadsheets provides robust support for VBA macros, allowing you to seamlessly run automation scripts to consolidate data from multiple workbooks just like you would in Microsoft Excel.

1. Open a Master File: Launch WPS Spreadsheets and create a new master workbook where your consolidated data will live.
2. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'WPS Macro' or 'Visual Basic' to open the script editor.
3. Paste Your Script: Insert a new module and paste your folder-looping consolidation script.
4. Execute the Consolidation: Run the macro to automatically pull and append data from your target folder into the active WPS worksheet.

Fully compatible with Microsoft Excel VBA formats (.xlsm and .xlsb).Easily automate repetitive data extraction and consolidation tasks.Lightweight architecture ensures fast processing even when looping through multiple files.Free to download and provides a highly familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I specify which rows to copy in the VBA script?

You can adjust the `Range("A2:W13")` part of your VBA script to target the exact rows and columns you want to extract from the source workbooks. Modify the row and column indicators as needed.

Will this macro work if the source workbooks have different sheet names?

Yes, if your macro uses `Worksheets(1)`, it will reference the first sheet sequentially regardless of its name. If your target data is on a differently positioned sheet, you will need to modify the script to reference the sheet by its exact name, like `Worksheets("DataSheet")`.

Do I need to enable macros to run this code?

Yes, you must ensure macros are enabled in your Trust Center settings, or click 'Enable Content' in the security warning bar when opening the master workbook containing the VBA script.

Can I use this VBA script in WPS Office?

Absolutely. WPS Office Spreadsheets fully supports VBA macros. You can run the exact same `Dir` and looping script to consolidate data across multiple workbooks within WPS.

Excel & Data Problems