How to Merge Multiple Excel Sheets into One Workbook?

Working with multiple Excel sheets at the same time is quite difficult, whether you are handling sales reports, employee records, invoices, survey results, or monthly business data. Combining complete data into one workbook helps to organize data properly and increase productivity.

If you are also one of those who want to merge multiple Excel sheets into one workbook, then this is the right guide. Here we will learn how to combine Excel files into one file, common challenges, manual and automated merging methods. Let’s break down the article one by one.

Why Do Users Prefer Automated Tools?

Users prefer the tool because they can:

• Merge multiple files in bulk at once.
• Preserve original formatting and hierarchy.
• Easily handle large files without hassle.
• Reduce manual efforts.
• Also, avoid data corruption issues.
• Work in an offline environment for better security.

How to Merge Excel Sheets into One Manually?

Yes, we can use three different methods to merge multiple Excel sheets into one workbook: using VBA code, copy-paste data, and using Power Query. Each technique is explained below one by one in detail:

Method 1: Copy-Paste Data

Simplest techniques to combine Excel sheets into one:

1. Open your Excel book.
2. Right-click on the sheet tab.
3. Choose the Move or Copy option.
4. Select the target or new workbook.
5. Check the Create a Copy option.
6. Hit the OK icon.
7. Repeat the process for each sheet.

Method 2: Power Query

It’s an advanced feature available in modern Excel versions.

1. Open your Excel.
2. Head to the “Data” >> “Get Data”.
3. Select “From File” >> “From Workbook”.
4. Choose the Excel file.
5. Now load data in Power Query Editor.
6. Next, combine and transform sheets.
7. Finally, export the merged results.

Method 3: Merge Multiple Excel Sheets into One Workbook with VBA Code

The advanced and best method for tech-savvy users to combine Excel files into one smoothly.

1. Open VBA mode using “Alt + F11” keys.
2. And insert a new module.
3. Use the given code:
   

   Sub MergeMultipleSheetsIntoOneWorkbook()

   Dim SourceWorkbook As Workbook
   Dim TargetWorkbook As Workbook
   Dim SourceSheet As Worksheet
   Dim FolderPath As String
   Dim FileName As String

   ‘Select folder containing Excel files
   With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = “Select Folder Containing Excel Files”
   If .Show <> -1 Then Exit Sub
   FolderPath = .SelectedItems(1) & “\”
   End With

   ‘Create new workbook for merged sheets
   Set TargetWorkbook = Workbooks.Add

   ‘Get first Excel file
   FileName = Dir(FolderPath & “*.xls*”)

   ‘Loop through all Excel files
   Do While FileName <> “”

   Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)

   ‘Copy all sheets to target workbook
   For Each SourceSheet In SourceWorkbook.Worksheets
   SourceSheet.Copy After:=TargetWorkbook.Sheets(TargetWorkbook.Sheets.Count)
   Next SourceSheet

   SourceWorkbook.Close SaveChanges:=False

   FileName = Dir

   Loop

   ‘Delete default blank sheet if exists
   Application.DisplayAlerts = False

   If TargetWorkbook.Sheets.Count > 1 Then
   On Error Resume Next
   TargetWorkbook.Sheets(1).Delete
   On Error GoTo 0
   End If

   Application.DisplayAlerts = True

   MsgBox “All Excel sheets merged successfully!”, vbInformation

   End Sub

4. And run the macro.

Professional Solution to Merge Multiple Excel Sheets into One Workbook

As we mentioned, use the professional solution for multiple small Excel files. Because the manual method contains multiple drawbacks. Users can easily use the tool for handling multiple files. It provides multiple features and benefits, which are given in the upcoming sections.

How to Merge Excel Sheets into One? Working Steps

1. Download and start the Excel merger tool on your system, and choose the Merge Excel option.

   

2. Import your single or multiple files through the Add Files and Add Folders options.

   

3. Choose Merge Mode, the saving location, and click on the Merge option.

   

4. After completing the procedure, click on the “OK” button.

   

Conclusion

Here, we have explained the possible methods to merge multiple Excel sheets into one workbook using manual and automated solutions. Manual methods are good for limited files, while software is ideal for multiple large files. Manual techniques contain multiple drawbacks, while software only provides the best features. Users can use the trial edition to understand how the tool works to combine Excel files into one.