Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to merge worksheets / workbooks into one worksheet?

When we use Excel, we usually need to merge multiple worksheets or workbooks into a single master worksheet, so that we can analyze and calculate the data quickly and easily. For general users, we couldn’t finish it except using copy and paste command, it is easy to combine few worksheets, but if there are numerous worksheets, this method will be time-consuming. Today, I will talk about some quick tricks for you to solve this problem.

Merge all worksheets of active workbook into one worksheet with VBA code

Merge worksheets or workbooks into one worksheet with Kutools for Excelgood idea3

Merge two tables and update by column with Kutools for Excelgood idea3

Easily Combine multiple sheets/Workbook into one Single sheet or Workbook

To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only.  Click for 60 days free trial!
combine sheets
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

Merge all worksheets of active workbook into one worksheet with VBA code


Supposing you have three worksheets (Grade1, Grade2, Grade3) which contain some information of the students, and now you need to merge them into one worksheet. See following screenshots:

doc-merge-worksheets-into-one1
doc-button2



doc-merge-worksheets-into-one4




doc-button1
doc-merge-worksheets-into-one2
doc-button1
doc-merge-worksheets-into-one3

The following VBA code can help you to get data from all worksheets of active workbook together into a new single worksheet. At the same time, all of the worksheets must have the same field structure, same column headings and same column order. Please do as follows:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

3. Then press F5 key to run the code, and all the data in the workbook has been merged into a new worksheet named Combined which will add before all worksheets.

Notes:

(1.) Your data must start from A1, if not, the code will not take effect.

(2.) Your data must have the same structure.

(3.) This code only can combine all worksheets of the active workbook, if you want to merge worksheets from multiple workbooks, this code will not work.


Merge worksheets or workbooks into one worksheet with Kutools for Excel

Maybe the above VBA code has some limitations for you, Kutools for Excel’s Combine utility can help you merge worksheets from a workbook or multiple workbooks quickly and easily, whether they have the same structures or not.

Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

Supposing you have two workbooks need to be merged, and the worksheets have not same structures, as following screenshots shown, in this case, Kutools for Excel’s Combine tool also can solve this problem.

doc-merge-worksheets-into-one7   doc-merge-worksheets-into-one8

After installing Kutools for Excel, please do as following steps:

1. Click Kutools Plus > Combine, and a dialog pops out to remind you the opened workbook cannot be combine, see screenshots:
doc merge tables 9

doc combine sheets into one sheet 2

2. Click OK to go on, in the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one worksheet option. See screenshot:
doc combine sheets into one sheet 3

3. And click Next button, on step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, click Add button to insert the files you want to combine. Then specify the worksheets that you want to combine and also you can click button1 button to select the range that you want to use. (By default, the used range is selected for each worksheet) See screenshot:

doc combine sheets into one sheet 4
 doc arrow down
 doc combine sheets into one sheet 5
 doc arrow down
 doc combine sheets into one sheet 6

4. After specify the worksheets and ranges, then continue to click Next button, on step3 of wizard, do the following settings as your need, and see screenshot:doc combine sheets into one sheet 7

5. After finishing the settings, click Finish button to combine the worksheets, and then you can open the combined workbook directly by clicking Yes.
doc merge tables 10
doc combine sheets into one sheet 9

6. And you will be asked if you want to save this scenario,If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshot), and when you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard. If you don’t want to save this scenario, please click No. see screenshot:
doc-merge-worksheets-into-one11

doc merge worksheets into one sheet 9

And then all the selected worksheets in the workbooks have been merged into a new worksheet of a workbook. The worksheet information has been added before the data. See screenshot:
doc combine sheets into one sheet 10

If you want to merge multiple worksheets into one workbook, you can apply check Combine multiple worksheets from workbooks into one worksheet option in Combine Worksheets step one.
doc merge tables 1


Merge two tables and update by column with Kutools for Excel

If you want to merge two tables from two sheets in one or different workbook into one and update the data based on a column as below screenshot shown, you can apply Kutools for Excel’s Tables Merge function to quickly merge tables and update data across sheets.
doc merge tables 2

After free installing Kutools for Excel, please do as below:

1. Select the table as main table you want to update first, table1, then click Kutools Plus > Tables Merge.
doc merge tables 3

2. In the Tables Merge first 1 step dialog, select the lookup table, table2. If the tables have headers, check the header options.
doc merge tables 4

3. Click Next, select the column you want to update data in table1 based on.
doc merge tables 5

4. In Tables Merge step 3, select the columns in main table (table1) that you want to update data.
doc merge tables 6

5. Click Next, select the column you want to add to main table (table1) based on lookup table (table2).
doc merge tables 7

6. In last step, you can choose the options to operate the merge result.
doc merge tables 8

7. Click Finish, now the main table has been updated and added based on lookup table.


arrow blue right bubbleRelative Articles:

How to combine multiple workbooks to one workbook in Excel?

How to combine worksheets of same name into one worksheet?

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


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shannon · 4 years ago
    Works like a charm, thanks for sharing!
  • To post as a guest, your comment is unpublished.
    Jason Mills · 4 years ago
    used your first Macro function. WOW! thanks for posting
    • To post as a guest, your comment is unpublished.
      Edwin M · 4 years ago
      I think the code '[b]Selection.Offset(0, 0).Resize(Selection.Rows.Count - 1).Select[/b]' has not to be there. On my end, what it does is select the second row onwards missing the first.
      • To post as a guest, your comment is unpublished.
        Razina K · 2 years ago
        I have the same problem. It's omitting the first row from the second worksheet added.
        • To post as a guest, your comment is unpublished.
          KJ · 1 years ago
          Eliminates headers so you don't have duplicated headers