Answer

To merge Excel files into one workbook, use one of the following methods depending on how many files you have, whether you want to keep worksheet names, and whether you want an automatic repeatable process.

Method 1 — Manual: Open files and Move or Copy sheets (quick for a few files)

1. Open the destination workbook where you want all sheets combined.

2. Open a source workbook that contains sheets to move.

3. Right-click the sheet tab in the source workbook and choose Move or Copy….

4. In the dialog, set To book: to the destination workbook.

5. Choose where to place the sheet in Before sheet:.

6. Check Create a copy when you want to keep the sheet in the source file.

7. Press OK.

8. Repeat for each sheet or file.

Windows vs Mac differences

1. On Windows, the dialog label is Move or Copy… and looks the same across Excel versions.

2. On Mac, the dialog is identical in newer Excel for Mac builds; in very old Mac Excel versions, use the menu Edit → Move or Copy Sheet.

Method 2 — Power Query (recommended for many files, repeatable, safe)

To merge many files that share a similar structure (same columns), use Power Query's From Folder flow.

1. Place all source Excel files in a single folder.

2. In the destination workbook choose Data → Get Data → From File → From Folder.

3. Browse to the folder and click Open.

4. In the dialog click Combine → Combine & Transform Data.

5. In the Combine Files preview choose the worksheet or table to extract from each file.

6. Power Query Editor opens. Use steps to filter, rename, or remove columns.

7. Click Close & Load to load combined data into a sheet or data model.

Notes

1. Power Query treats each file as a data source. It is ideal when the files have identical column layout.

2. When new files are added to the folder, refresh the query to import them automatically.

3. On Mac, Power Query is available in recent Excel for Mac builds; some older Mac versions lack full Power Query support.

Method 3 — Copy/paste special (values only) (for combining data ranges)

1. Open source workbook and select the data range (no sheet-level merge).

2. Press Ctrl+C (Windows) or ⌘+C (Mac) to copy.

3. Switch to the destination workbook, select the top-left target cell.

4. Right-click and choose Paste Special → Values when you want only data and not formatting or formulas.

5. Repeat for each file.

Method 4 — VBA macro to combine sheets or worksheets from many files (repeatable, flexible)

To combine all worksheets from every workbook in a folder into the active workbook, run this macro.

1. Save and close other Excel workbooks you do not want processed.

2. Press Alt+F11 (Windows) or Fn+Option+F11 (Mac) to open VBA Editor.

3. Insert a new Module: Insert → Module.

4. Paste the code below into the module.

5. Save the destination workbook as a macro-enabled file (.xlsm).

6. Run the macro MergeAllWorkbooksInFolder.

   Sub MergeAllWorkbooksInFolder()

       Dim FolderPath As String

       Dim Filename As String

       Dim wbSource As Workbook

       Dim ws As Worksheet

       Application.ScreenUpdating = False

       Application.EnableEvents = False

       

       FolderPath = GetFolder()

       If FolderPath = "" Then

           MsgBox "No folder selected.", vbExclamation

           GoTo Finish

       End If

       

       Filename = Dir(FolderPath & "*.xls*")

       Do While Filename <> ""

           On Error Resume Next

           Set wbSource = Workbooks.Open(FolderPath & Filename, ReadOnly:=True)

           If Err.Number <> 0 Then

               Err.Clear

               Filename = Dir()

               GoTo ContinueLoop

           End If

           On Error GoTo 0

           

           For Each ws In wbSource.Worksheets

               ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

               ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = _

                   GetUniqueName(ThisWorkbook, ws.Name)

           Next ws

           

           wbSource.Close SaveChanges:=False

   ContinueLoop:

           Filename = Dir()

       Loop

   
   

   Finish:

       Application.EnableEvents = True

       Application.ScreenUpdating = True

       MsgBox "Merge complete.", vbInformation

   End Sub

   
   

   Function GetFolder() As String

       Dim fldr As FileDialog

       Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

       With fldr

           .Title = "Select Folder with Excel files"

           .AllowMultiSelect = False

           If .Show = -1 Then

               GetFolder = .SelectedItems(1) & Application.PathSeparator

           Else

               GetFolder = ""

           End If

       End With

       Set fldr = Nothing

   End Function

   
   

   Function GetUniqueName(wb As Workbook, baseName As String) As String

       Dim nameTry As String

       Dim i As Long

       nameTry = baseName

       i = 1

       On Error Resume Next

       Do While Not wb.Sheets(nameTry) Is Nothing

           Err.Clear

           i = i + 1

           nameTry = baseName & "_" & i

       Loop

       On Error GoTo 0

       GetUniqueName = nameTry

   End Function

VBA notes

1. The macro copies every worksheet into the destination workbook and renames duplicates by adding an index.

2. Save the destination workbook as .xlsm to keep the macro.

3. Mac Excel requires enabling macros in Preferences; folder picker code works in recent Mac builds.

Method 5 — Use Excel’s Get & Transform + Append Queries for multiple worksheets with different names

1. Use Data → Get Data → From File → From Workbook to pull one file.

2. In Power Query Editor, transform it into a standard table.

3. Choose Home → Append Queries → Append Queries as New.

4. Add the other queries (from other files or sheets).

5. Close & Load when complete.

When should you use each method?

1. Use Method 1 for a handful of sheets where manual control of placement and formatting is needed.

2. Use Method 2 for many files with a consistent structure and when you want a repeatable, refreshable process.

3. Use Method 3 to paste static values only.

4. Use Method 4 to automate merging many files or when worksheet structures vary, and you need sheet-level merges.

5. Use Method 5 when you must transform each source before combining.

Common pitfalls and how to avoid them

1. Duplicate sheet names cause errors. Resolve by renaming or using code that enforces unique names.

2. Hidden or protected sheets may not be copied. Unhide or unprotect before merging.

3. Different table/column structures break Power Query's combine. Standardize column headers or transform in the query.

4. External links remain when copying formulas. Use Paste Special → Values to remove links.

5. Very large files cause slow performance. Use Power Query and load into the data model to improve speed.

Quick checklist before merging (1–6)

1. Back up source files.

2. Place files to be merged in a single folder when using Power Query or VBA.

3. Decide whether you need formulas or values only.

4. Ensure worksheets are not protected or hidden.

5. Confirm the destination workbook has enough sheet name flexibility.

6. Save destination workbook as .xlsx for no macros, .xlsm if using VBA.

Troubleshooting questions — Question and answer

Will merging change the original files?

Yes. Moving sheets without checking Create a copy will remove the sheet from the source workbook. Use Create a copy to preserve originals.

Can I merge only certain sheets from many files?

Yes. Use Power Query and choose the target worksheet or table, or use a VBA filter to copy only sheets whose names match a pattern.

Do formulas keep working after merging?

Yes and no. Formulas will remain, but external workbook references will point to the original file unless you convert formulas to values or update references manually.

Short sample VBA to append used ranges as new sheets (alternate)

Sub AppendUsedRangesAsSheets()

    Dim ws As Worksheet, newWs As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        Set newWs = Worksheets.Add(After:=Worksheets(Worksheets.Count))

        newWs.Name = "Copy_" & ws.Index

        ws.UsedRange.Copy

        newWs.Range("A1").PasteSpecial xlPasteValues

    Next ws

    Application.CutCopyMode = False

    MsgBox "Copied used ranges as value-only sheets.", vbInformation

End Sub

1. Use Power Query for repeatable merges with consistent data layout.

2. Use the VBA approach for full workbook or sheet-level automation across many files.

3. Keep backups before any large merge operation.

4. Test the chosen method on a small sample of files before committing to the full set.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report