Excel Tricks-2 Ways-Combine & Merge Multiple Excel File Data Into One With VBA Code

1. Merge Excel Workbooks From A Folder In One File

Below is the VBA code to merge multiple excel files which are entered in a folder in “D” Drive & the Folder name is “Files”. So if your folder name is different then you can change the path according to your drive.

Input Data

Final Output After Merge


Sub MergeWorkbooks()

Dim FolderPath As String

Dim File As String

Dim i As Long

FolderPath = “D:\Files\”

File = Dir(FolderPath)

Do While File <> “”

                   Workbooks.Open FolderPath & File

        ActiveWorkbook.Worksheets(1).Copy _


        ActiveSheet.Name = Replace(File, “.xlsx”, “”)


    File = Dir()


End Sub

2. Consolidate Excel All Sheets Data In One File

This VBA code will help you to consolidate the All Excel Sheets data in One File, but the limitation is that if you excel data in one file but different-different sheets. then this code will consolidate all sheets data from one to a single sheet.

Input Data

Final Output After Merge

Leave a Reply

Your email address will not be published. Required fields are marked *