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

VBA CODE

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 _

        after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

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

        Workbooks(File).Close

    File = Dir()

Loop

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