Combine worksheets with same name from multiple workbooks into one

Merging data from multiple Excel workbooks into a single sheet is a common requirement in data analysis, especially when dealing with consolidated reporting or data aggregation from various sources. This can be particularly challenging when you need to combine worksheets that have the same name across different workbooks. This tutorial will guide you through two effective methods to streamline this process: using VBA code for those who prefer a hands-on, programmable approach, and utilizing Kutools for Excel for users seeking a more user-friendly, automated solution.

• Combine worksheets with same name into one with VBA code
• Easily combine worksheets with same name into one with Kutools for Excel

Combine worksheets with same name into one with VBA code

This section provides a VBA code for combining worksheets with the same name from multiple workbooks into one, please do as follows:

1. Press "Alt" + "F11" to open the VBA editor.
2. In the VBA editor, click "Insert" > "Module", and then enter the following VBA code into the module.
   VBA code: Merge workbooks with same name into one

   Sub CombineSameNameSheetsFromMultipleWorkbooks()
   'Updated by Extendoffice 2025/3/27
       Dim wsTarget As Worksheet
       Dim wbTarget As Workbook
       Dim wbSource As Workbook
       Dim wsSource As Worksheet
       Dim strSheetName As String
       Dim fd As FileDialog
       Dim FileChosen As Integer
       Dim FileName As String
       Dim lastRow As Long
   
       ' Set the name of the sheets to combine
       strSheetName = "SheetName" ' Change "SheetName" to the name of your sheet
   
       Set wbTarget = Workbooks.Add
       Set wsTarget = wbTarget.Sheets(1)
       wsTarget.Name = "Combined Data"
   
       Set fd = Application.FileDialog(msoFileDialogFilePicker)
       fd.AllowMultiSelect = True
   
       FileChosen = fd.Show
       If FileChosen = -1 Then
           For Each varFile In fd.SelectedItems
               Set wbSource = Workbooks.Open(varFile)
               
               On Error Resume Next
               Set wsSource = wbSource.Sheets(strSheetName)
               On Error GoTo 0
               
               If Not wsSource Is Nothing Then
                   lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
                   
                   wsSource.UsedRange.Copy Destination:=wsTarget.Cells(lastRow + 1, 1)
                   
                   Set wsSource = Nothing
               End If
               wbSource.Close False
           Next varFile
       End If
       MsgBox "Data has been combined successfully!", vbInformation
   End Sub

   Note: Before running the code, you need to change "SheetName" in the "strSheetName" variable to the actual name of the sheets you want to combine.

   

3. Press "F5" to run the code. A "Select the Excel files" window will appear, please choose the workbooks that contain the worksheets with the same name you want to combine, then click the "Open" button.

   

4. Once the merging is complete, a "Microsoft Excel" dialog box will appear to confirm that the worksheets have been successfully combined. Click "OK" to close it.

   

Now, the content of all worksheets with the same name from the selected workbooks has been merged into a single worksheet in a new workbook.

Easily combine worksheets with same name into one with Kutools for Excel

While VBA offers powerful automation capabilities in Excel, it can be complex and intimidating for users without programming skills. Additionally, VBA requires careful security practices and can present compatibility issues. In contrast, "Kutools for Excel" provides a more user-friendly and secure alternative to simplify the process of combining worksheets from multiple workbooks, eliminating the need for complex coding and making the task accessible to all Excel users.

After installing Kutools for Excel, select "Kutools Plus" > "Combine" to open the "Combine Worksheets" wizard. Then configure as follows.

1. In the "Combine Worksheets – Step 1 of 3" wizard, select the "Combine all same name worksheets into one worksheets" option and select "Next".

   

2. In the "Step 2 of 3" wizard, click the "Add" button to select the workbooks that contain the worksheets with the same name you want to combine.
   Note: If the workbooks are saved in a folder, you can click the drop-down arrow next to the "Add" button to expand the submenu, then select "Folder" to quickly add all Excel files from that folder.

   

3. After adding the workbooks, you have two options depending on your needs:
   • If you want to combine all worksheets from the selected workbooks into one workbook, simply click the "Next" button. All worksheets will be combined, and the content of same-name worksheets will be merged into a single worksheet.
   • If you want to combine only the worksheets with the same name, first select a worksheet in the "Worksheet List" section, then click the "Same Sheet" button. This will automatically select all worksheets with the same name across the added workbooks. After that, click "Next" to proceed.

   

4. In the final step of the wizard, either keep the default option selected or customize the settings according to your needs, then click "Finish".

   

5. Choose a destination folder for the combined workbook, enter a file name as desired, and then click the "Save" button to complete the operation.

   

Combining worksheets with the same name from multiple workbooks can be done efficiently with the right tools. Whether you prefer built-in methods or advanced add-ins, both approaches provide reliable solutions to suit different skill levels. Mastering these techniques will significantly boost your productivity and streamline data consolidation in Excel.

If you want to know more about this feature, please view this page: Combine all same name worksheets into one worksheet tutorial.

Related articles:

How to combine multiple workbooks to one workbook in Excel?

How to merge worksheets / workbooks into one worksheet?

How to summarize data from worksheets / workbooks into one worksheet?

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

More details of Kutools for Excel... Free Download...

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

More details of Office Tab... Free Download...

---

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

• All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
• One installer, one license — set up in minutes (MSI-ready)
• Works better together — streamlined productivity across Office apps
• 30-day full-featured trial — no registration, no credit card
• Best value — save vs buying individual add-in

More details of Kutools for Office... Free Download...