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

Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free trial of Office Tab!

ot excel

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.
    arul kumar · 3 months ago
    Hi all,

    I have used the below mentioned code, In my data some blank Rows is there for each tab. Data is not coping after the blank rows. How to combine the all data after the blank rows. Please advise.

    Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A16").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A17").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

    Thanks
    Arul
    • To post as a guest, your comment is unpublished.
      Sunny · 1 months ago
      You can try below code, it will merge all data and skip blank rows.

      Sub CombineForm()
      Dim xI, xMax As Integer
      Dim xCWS, xWS As Worksheet
      Dim xObjRRange, xObjCRange, xObjSR As Range
      On Error Resume Next
      Sheets(1).Select
      Set xCWS = Worksheets.Add
      xCWS.Name = "Combined"
      Sheets(2).Activate
      Range("A1").EntireRow.Select
      Selection.Copy Destination:=xCWS.Range("A1")
      For xI = 2 To Sheets.Count
      Set xWS = Sheets(xI)
      xWS.Activate
      Set xObjRRange = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
      Set xObjCRange = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
      Set xObjSR = Range(Cells(1, 1), Cells(xObjRRange.Row, xObjCRange.Column))
      xObjSR.Select
      Selection.Copy Destination:=xCWS.Range("A65536").End(xlUp)(2)
      Next
      xMax = xCWS.Range("a65536").End(3).Row
      xCWS.Range("a1:a" & xMax).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      End Sub
      • To post as a guest, your comment is unpublished.
        arul kumar · 1 months ago
        Thank you, Sunny! This coding is working fine..

        But i want to combine all the sheets from row no: 16. Could you help us to update the coding.

        Thanks
        Arul
        • To post as a guest, your comment is unpublished.
          Sunny · 1 months ago
          The below code will combine sheets from the second row, you can change the number 2 (xStart = 2) to other number for you need.

          Sub CombineForm()
          Dim xStart, xFNum, xIntCFMax, xIntRRg, xIntCRg As Integer
          Dim xCFWS, xWS As Worksheet
          Dim xObjSR As Range
          Dim xStrName As String
          On Error Resume Next
          Application.ScreenUpdating = False
          xStart = 2
          xStrName = "CombinedForm"
          Set xWS = Sheets(xStrName)
          If xWS Is Nothing Then
          xWS.Delete
          End If
          Set xCFWS = Worksheets.Add
          xCFWS.Name = xStrName

          For xFNum = 1 To Sheets.Count
          Set xWS = Sheets(xFNum)
          If xWS.Name <> xStrName Then
          xWS.Activate
          xIntRRg = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
          xIntCRg = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
          If xStart <= xIntRRg Then
          Set xObjSR = Range(Cells(xStart, 1), Cells(xIntRRg, xIntCRg))
          xObjSR.Select
          Selection.Copy Destination:=xCFWS.Range("A65536").End(xlUp)(2)
          End If
          End If
          Next
          Application.ScreenUpdating = True
          End Sub
      • To post as a guest, your comment is unpublished.
        arul · 1 months ago
        Thank you, Sunny!

        I want to combine the sheet from row no:16. Could you help me to update the coding.

        Thanks
        Arul
  • To post as a guest, your comment is unpublished.
    Neno · 6 months ago
    My headings start on row 26, the first 25 rows are occupied with the parameters I used to generate my reports, it would take a long time to delete these since there are many reports generated. Using the VBA code above, how could I configure this code to exclude the first 25 rows, capture the headings on row 26 and combine all the data under the headings (row 27 onwards) for each worksheet?
    • To post as a guest, your comment is unpublished.
      Bruno G. · 5 months ago
      Change all the "A1" in the code to "A26". Save your data before trying this change, just in case!
  • To post as a guest, your comment is unpublished.
    Tina · 8 months ago
    Hi.. The VBA code works fine. Thank you! But can you please help on how I can edit the code so that only 2 sheets will be combined? I have 5 sheets in my workbook but I only need to combine 2 of them.
    • To post as a guest, your comment is unpublished.
      Nigah · 8 months ago
      Just save as the current workbook with a new name and delete other three worksheets which you don't want to combine, now you have only your desired two sheets for VBA code execution.
  • To post as a guest, your comment is unpublished.
    deepak · 9 months ago
    hello everyone

    I want to merged more workbook data in one so give me suggest
  • To post as a guest, your comment is unpublished.
    oduff · 9 months ago
    Hi everyone,

    First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.

    I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.

    In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" (Overview). However there is a different column in the sheet and it's between "Nr." and "Thema" columns (which are in A1 and A2 in all the 9 Sheets) and this different column called "Kategorie" (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this "Nr. (A1), Kategorie (A2) and Thema (A3).....".So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" (Overview) sheet needs to update by itself. How can I do this?

    P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.

    I hope I explained it well. Thanks a lot in advance!

    I wish you merry Christmas and a happy new year!

    oduff