Merge Hundreds of Excel Files with One Click
If you need to combine hundreds or thousands of Excel files (such as GSTR-2B data, sales reports, or monthly invoices) into a single workbook, manually copying and pasting is time-consuming and highly prone to errors. I am sharing a VBA macro that automates this entire process. It opens all your Excel files, takes the header from the first file only, and appends the data (starting from row 2) from all subsequent files. No data is lost, and duplicate headers are completely eliminated!
📘 How to Use (Step-by-Step)
🔧 Step 1: Open the VBA Editor
Open Microsoft Excel on your computer (any blank workbook will do).
Press Alt + F11 on your keyboard. (A new window will open).
📄 Step 2: Insert a Module
In the VBA window, go to the top menu and click Insert → Module. (A blank white space will appear).
📋 Step 3: Copy and Paste the Code
Copy the complete code from the box below and paste it into the blank space (Ctrl+V).
Sub MergeAllExcelFiles_NoDuplicateHeaders()
Dim folderPath As String
Dim fileName As String
Dim wbMaster As Workbook
Dim wbSource As Workbook
Dim wsMaster As Worksheet
Dim wsSource As Worksheet
Dim lastRowMaster As Long
Dim lastRowSource As Long
Dim sheetNames As Variant
Dim i As Integer
Dim firstFile As Boolean
' ---------- Sheets to merge (Modify this array if your sheet names differ) ----------
sheetNames = Array("B2B", "B2BA", "CDNR", "CDNRA", "ECO", "ECOA", "ISD", "ISDA", "TDS", "TDSA", "TCS", "IMPG", "IMPG SEZ")
' ---------- Select the folder containing your files ----------
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the folder containing your Excel files"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
folderPath = .SelectedItems(1) & "\"
End With
' ---------- Create the Master Workbook ----------
Set wbMaster = Workbooks.Add
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' Create sheets in the master file
For i = LBound(sheetNames) To UBound(sheetNames)
On Error Resume Next
Set wsMaster = wbMaster.Sheets(sheetNames(i))
If wsMaster Is Nothing Then
wbMaster.Sheets.Add(After:=wbMaster.Sheets(wbMaster.Sheets.Count)).Name = sheetNames(i)
End If
On Error GoTo 0
Next i
' Delete default sheets (Sheet1, Sheet2, Sheet3)
For i = wbMaster.Sheets.Count To 1 Step -1
If wbMaster.Sheets(i).Name <> "B2B" And wbMaster.Sheets(i).Name <> "B2BA" And _
wbMaster.Sheets(i).Name <> "CDNR" And wbMaster.Sheets(i).Name <> "CDNRA" And _
wbMaster.Sheets(i).Name <> "ECO" And wbMaster.Sheets(i).Name <> "ECOA" And _
wbMaster.Sheets(i).Name <> "ISD" And wbMaster.Sheets(i).Name <> "ISDA" And _
wbMaster.Sheets(i).Name <> "TDS" And wbMaster.Sheets(i).Name <> "TDSA" And _
wbMaster.Sheets(i).Name <> "TCS" And wbMaster.Sheets(i).Name <> "IMPG" And _
wbMaster.Sheets(i).Name <> "IMPG SEZ" Then
wbMaster.Sheets(i).Delete
End If
Next i
firstFile = True ' Flag to identify the first file
' ---------- Start searching for Excel files in the folder ----------
fileName = Dir(folderPath & "*.xlsx")
Dim fileCount As Integer
fileCount = 0
Do While fileName <> ""
fileCount = fileCount + 1
Application.StatusBar = "Processing: " & fileCount & " - " & fileName
On Error Resume Next
Set wbSource = Workbooks.Open(folderPath & fileName, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
On Error GoTo 0
If Not wbSource Is Nothing Then
For i = LBound(sheetNames) To UBound(sheetNames)
On Error Resume Next
Set wsSource = wbSource.Sheets(sheetNames(i))
On Error GoTo 0
If Not wsSource Is Nothing Then
lastRowSource = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
If lastRowSource > 1 Then
Set wsMaster = wbMaster.Sheets(sheetNames(i))
lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row
' ---------- Core Logic: Copy Header from 1st file, Data only from the rest ----------
If firstFile = True Then
' 1st file: Copy everything including header (from row 1)
wsSource.Rows("1:" & lastRowSource).Copy Destination:=wsMaster.Rows(1)
Else
' 2nd and subsequent files: Copy data only (from row 2)
If lastRowSource >= 2 Then
wsSource.Rows("2:" & lastRowSource).Copy Destination:=wsMaster.Rows(lastRowMaster + 1)
End If
End If
End If
End If
Next i
wbSource.Close SaveChanges:=False
firstFile = False ' Flag turned off after the first file
End If
fileName = Dir() ' Move to the next file
Loop
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "✅ Completed! Merged " & fileCount & " files successfully." & vbCrLf & _
"Headers were taken only from the first file." & vbCrLf & _
"Save the file now (Ctrl+S).", vbInformation, "Success!"
End Sub▶️ Step 4: Run the Code
Press F5 on your keyboard (or click the green 'Play' button in the toolbar).
A folder selection dialog will appear – select the folder where your Excel files are stored.
💾 Step 5: Save the File
Once the macro finishes, a new Excel file (the Master Workbook) will be created with all your combined data.
Press Ctrl + S to save it. Name it something like Merged_GST_Data.xlsx.
⚠️ Important Tips & Cautions
🔒 Enable Macros: If Excel shows a security warning when you open the file, make sure to click "Enable Content" or "Enable Macros". Otherwise, the code will not run.
📁 File Format: By default, the code searches for `.xlsx` files. If you have older `.xls` files, simply change `*.xlsx` to `*.xls` in the code. For both, use `*.xls*`.
🧩 Sheet Names: The code is pre-configured with standard GSTR-2B sheet names (B2B, CDNR, etc.). If your files use different sheet names, modify the `sheetNames = Array("...")` line accordingly.
⏳ Processing Time: Merging 1000 files can take anywhere from 5 to 15 minutes depending on file sizes and your system's performance. Watch the status bar at the bottom of Excel to track the progress. Please be patient and do not close Excel midway.
📌 Header Missing? If your first file does not have a header row, the macro will treat the first row as data. Please ensure the first file in your folder has the correct header structure.
