Combine Multiple Excel Files into One Workbook (Power Query & VBA)

Combine Multiple Excel Files into One Workbook (Power Query & VBA)

Overview

Two reliable ways to combine multiple Excel files into a single workbook:

• Power Query: Built-in, user-friendly, ideal for combining tables or consistent-structured sheets. No coding required.
• VBA: Scriptable and flexible, best when you need custom copying (specific sheets, formats, or complex transformations).

When to use which

• Power Query: many files with consistent table structure; you want refreshable, repeatable merges; minimal coding.
• VBA: files have varying layouts, you need to preserve sheet formatting, or you require custom placement/renaming logic.

Power Query — quick steps

1. Put all source files in one folder.
2. In Excel: Data → Get Data → From File → From Folder.
3. Choose the folder, then click Combine & Load (or Combine & Transform to edit).
4. In the Combine dialog choose the sheet/table to use as a sample.
5. Use Power Query editor to filter, rename columns, change types, remove headers/footers, or append steps.
6. Click Close & Load to load the combined data into a worksheet or data model.
7. To update with new files: add files to the folder and click Refresh.

Notes: Power Query combines tables best; if sheets aren’t formatted as tables, convert them or use the sheet view in the Combine dialog.

VBA — copy sheets into one workbook (basic script)

1. Open a new workbook where you want to collect sheets.
2. Press Alt+F11 → Insert → Module and paste this VBA:

vba
Copy CodeCopied
Sub CombineWorkbooks_Copiesheets() Dim FolderPath As String, FileName As String

 
Dim wbDest As Workbook, wbSource As Workbook FolderPath = "C:\Path\To\Files\" ' change to your folder Set wbDest = ThisWorkbook FileName = Dir(FolderPath & "*.xlsx") Application.ScreenUpdating = False Do While FileName <> ""     If FileName <> wbDest.Name Then         Set wbSource = Workbooks.Open(FolderPath & FileName)         Dim sh As Worksheet         For Each sh In wbSource.Worksheets             sh.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)             On Error Resume Next             ActiveSheet.Name = wbSource.Name & "_" & sh.Name             On Error GoTo 0         Next sh         wbSource.Close SaveChanges:=False     End If     FileName = Dir Loop Application.ScreenUpdating = True MsgBox "Done." 
End Sub 


Update FolderPath and run the macro. The script opens each workbook, copies all sheets into the current workbook, and renames them to avoid duplicates.

Tips & Best Practices

Back up files before running macros.
For Power Query, convert source ranges to Tables (Ctrl+T) for cleaner merges.
In VBA, handle name collisions and large files by adding error handling and incremental saves.
If you need to merge only data (not whole sheets), use Power Query or modify VBA to copy used ranges instead of entire sheets.
Consider file types (.xlsx vs .xlsm) and protected workbooks—unprotect before automating.

Common issues and fixes

Duplicate sheet names → rename or add prefix/suffix in code.
Different column orders → standardize columns in Power Query or align in VBA.
Large datasets slow → use Power Query with load to Data Model or process in batches in VBA.

Quick decision guide

Want no-code, refreshable merge of tabular data → Use Power Query.
Need full-sheet copying, custom naming, or special logic → Use VBA.