VBA & Macro Problems

How to Combine CSV Data from a Folder into One Master Sheet using Excel VBA

Emma Brown Oct 23, 2026 868 views

Question details

The user needs an Excel VBA macro to open multiple CSV files in a specific folder, find the last used row based on data in column B, and copy columns A through AQ into a single master sheet.

Combine CSV Data from a Folder into One Master Sheet Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating product data scattered across multiple CSV files into one comprehensive master workbook for streamlined analysis.
Observed behavior: The user requires a reliable VBA script to automate the merging process, but encountered file location issues when the source folder was synchronized on OneDrive.

Before you start

Verify that all your CSV files share the same column structure and that column B consistently contains data to accurately determine the last row. If your CSV folder is stored in a synced cloud directory like OneDrive, move it to a local drive (e.g., your Desktop or C: drive) to prevent path recognition errors in VBA.

Solution 1Recommended

Create a VBA Macro to Loop Through and Merge CSV Files

Use a VBA script leveraging the Dir function to iterate through all CSV files in a local folder, extracting data dynamically based on the last row of column B.

This solution automates the consolidation process. By checking column B in each CSV file, the macro ensures it only copies rows that contain actual product data, skipping empty spaces. It then pastes the designated range (Columns A to AQ) into the next available empty row on your master sheet.

Open the VBA Editor

Open your master Excel workbook. Press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a blank window for your code.

Write the Merging Code

Write a script utilizing the Dir function to loop through files (e.g., filename = Dir(folderPath & "*.csv")). Use a command like Cells(Rows.Count, "B").End(xlUp).Row to find the last row with data in column B for both the source CSV and the master sheet.

Define the Copy and Paste Range

Instruct the macro to copy Range("A2:AQ" & lastRow) from the CSV to skip the header, and paste it into the first empty row of the master sheet.

Update the Folder Path and Run

Crucially, hardcode your local folder path in the script (e.g., C:\Users\Name\Desktop\CSVFolder\). Close the VBA Editor, press ALT + F8, select your new macro, and click 'Run'.

Create a VBA Macro to Loop Through and Merge CSV Files

Resolve OneDrive Path Errors: If the macro fails to find the files, it is likely because OneDrive paths resolve as URLs in VBA. Moving your target folder to a strictly local path immediately resolves this issue.

Efficient Data Processing with WPS Spreadsheet

Automate Data Consolidation Using Macros in WPS Office

WPS Office provides robust built-in support for VBA and Macros, allowing you to automate repetitive tasks like merging multiple CSV files just as seamlessly. Enjoy advanced spreadsheet capabilities in a lightweight application.

1. Enable the Developer Tools: Open WPS Spreadsheet, navigate to the 'Developer' tab on the top ribbon, and ensure Macros are enabled in your settings.
2. Launch the VBA Editor: Click the 'Visual Basic' icon within the Developer tab to open the VBA programming environment.
3. Run Your Consolidation Script: Insert a new module, paste your CSV-merging VBA code, verify your local folder path, and press F5 to execute the automated data merge.

Fully supports standard VBA code and Macro executionSeamless format compatibility with Microsoft Excel .csv, .xls, and .xlsx filesLightweight architecture ensures fast processing even with large datasetsFamiliar developer interface makes it simple to write, debug, and run code

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail to locate files stored on OneDrive?

The VBA 'Dir' function relies on standard local directory paths. When files are synced via OneDrive, the path is often recognized as an HTTPS URL rather than a local file path, causing the macro to fail. Moving the files to a non-synced local folder fixes this.

How do I ensure the macro skips the header row in each CSV file?

You can skip the header by setting your copy range to start at row 2. Instead of copying the entire column, use a dynamic range like `Range("A2:AQ" & lastRow).Copy` to ensure headers from the individual CSVs are not repeatedly pasted into your master sheet.

How does VBA accurately find the last row containing data?

VBA typically uses a command like `Cells(Rows.Count, 2).End(xlUp).Row`. This acts as if you went to the very bottom of column B (column index 2) and pressed CTRL + UP ARROW, which jumps to the last cell that actually contains text or values.

Excel & Data Problems