VBA & Macro Problems

Combine Same-Named Worksheets from Multiple Workbooks with Excel VBA

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Combine data from worksheets with matching names across multiple Excel workbooks, copying each sheet's header only once.

Combine Same-Named Worksheets from Multiple Workbooks with Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A folder contains workbooks whose same-named worksheets should be consolidated into corresponding sheets in a new workbook.
Observed behavior
Each output worksheet should contain one header row followed by the data rows from its matching source worksheets.
Before you start

Place the source workbooks in one folder and close them in Excel before running the macro. Make sure each source sheet uses row 1 for headers and that matching sheets have columns in the same order.

Solution 1Recommended

Run a VBA macro to merge matching worksheets

Use this macro in desktop Excel to create a new workbook and append rows from sheets with matching names.

The macro copies values, not formatting or formulas. It uses the first nonempty occurrence of each sheet name for the header and skips row 1 in later occurrences.

The destination workbook is created in memory, so it is not included in the source file loop. Save it outside the source folder before running the merge again.

1
Open the VBA editor

In desktop Excel, open a workbook for the macro, then select Developer > Visual Basic. In the editor, choose Insert > Module.

2
Paste the macro

Paste this code into the module: Sub CombineMatchingSheets() Dim folder As String, fileName As String Dim wbT As Workbook, wbS As Workbook Dim wsT As Worksheet, wsS As Worksheet Dim lastCell As Range Dim lastRow As Long, lastCol As Long, nextRow As Long Dim hasSheets As Boolean Dim oldScreenUpdating As Boolean, oldSecurity As Long With Application.FileDialog(4) .Title = "Select the source workbook folder" If .Show <> -1 Then Exit Sub folder = .SelectedItems(1) End With If Right(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator End If oldScreenUpdating = Application.ScreenUpdating oldSecurity = Application.AutomationSecurity On Error GoTo Fail Application.ScreenUpdating = False Application.AutomationSecurity = 3 Set wbT = Workbooks.Add(xlWBATWorksheet) fileName = Dir(folder & "*.xls*") Do While fileName <> "" If Left(fileName, 2) <> "~$" And folder & fileName <> ThisWorkbook.FullName Then Set wbS = Workbooks.Open(Filename:=folder & fileName, UpdateLinks:=0, ReadOnly:=True) For Each wsS In wbS.Worksheets Set lastCell = wsS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not lastCell Is Nothing Then lastRow = lastCell.Row Set lastCell = wsS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) lastCol = lastCell.Column Set wsT = Nothing If hasSheets Then On Error Resume Next Set wsT = wbT.Worksheets(wsS.Name) On Error GoTo Fail End If If wsT Is Nothing Then If Not hasSheets Then Set wsT = wbT.Worksheets(1) hasSheets = True Else Set wsT = wbT.Worksheets.Add(After:=wbT.Worksheets(wbT.Worksheets.Count)) End If wsT.Name = wsS.Name wsT.Cells(1, 1).Resize(1, lastCol).Value = wsS.Cells(1, 1).Resize(1, lastCol).Value End If If lastRow > 1 Then Set lastCell = wsT.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) nextRow = 2 If Not lastCell Is Nothing Then If lastCell.Row >= 2 Then nextRow = lastCell.Row + 1 End If wsT.Cells(nextRow, 1).Resize(lastRow - 1, lastCol).Value = wsS.Cells(2, 1).Resize(lastRow - 1, lastCol).Value End If End If Next wsS wbS.Close SaveChanges:=False Set wbS = Nothing End If fileName = Dir Loop Application.AutomationSecurity = oldSecurity Application.ScreenUpdating = oldScreenUpdating If hasSheets Then MsgBox "Merge complete. Review and save the new workbook." Else MsgBox "No nonempty source worksheets were found." End If Exit Sub Fail: Dim errorMessage As String errorMessage = Err.Description On Error Resume Next If Not wbS Is Nothing Then wbS.Close SaveChanges:=False Application.AutomationSecurity = oldSecurity Application.ScreenUpdating = oldScreenUpdating On Error GoTo 0 MsgBox "Merge stopped: " & errorMessage & vbCrLf & "Review the new workbook for partial results.", vbExclamation End Sub

3
Run the merge

Place the cursor inside CombineMatchingSheets and select Run > Run Sub/UserForm. Choose the folder containing the source workbooks.

4
Review and save the result

Return to Excel, check the new workbook's sheets and headers, then select File > Save As and save it outside the source folder.

Run a VBA macro to merge matching worksheets
Check column layouts: The macro matches sheets by name but does not compare header labels or rearrange columns. It skips empty worksheets and temporary Excel lock files.
Free Microsoft Office alternative

Work with Excel-Compatible Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel workbooks. It supports common Office formats for a smooth transition, although it does not fix or guarantee compatibility with this Excel VBA macro.

Review and edit consolidated spreadsheet data in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar spreadsheet interface for everyday tasksMove between common Office document formats with a lightweight suite
microsoft office alternative - wps office

Frequently Asked Questions

How does the macro avoid repeating headers?

It copies row 1 when it first creates a destination sheet, then appends rows starting at row 2 from each matching source sheet.

What happens if a source workbook has a new sheet name?

The macro creates a corresponding worksheet in the new workbook and copies that source sheet's header and data.

Will the macro preserve formulas and formatting?

No. It transfers cell values only, so formulas become their current values and source formatting is not copied.

Can I run the macro again after saving the result?

Yes. Save the result outside the source folder first; otherwise, a later run may include the saved result as a source workbook.

Why might the merged columns contain mismatched data?

The macro appends columns by position, not by header name. Check that matching sheets use the same column order before running it.