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 count 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 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 Enterprise > Combine, and a dialog pops out to remind you the opened workbook cannot be combine, see screenshots:
doc combine sheets into one sheet 1

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 combine sheets into one sheet 8
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 know more about this feature, please click Combine multiple worksheets from workbooks into one worksheet


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.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jonas · 11 days ago
    Hello!
    I need your support. I have three sheets (A, B, C) into a worksheet. the sheet contain information on individuals.
    Sheet A: contains all individuals considered as ID with information (age, education, etc...)
    Sheets B et C: contain some individuals (ID) with information (Organisation, income, etc...)
    So I want to combine B and C to A to get all information from A,B, C together.

    Jonas
  • To post as a guest, your comment is unpublished.
    Jennifer · 1 months ago
    Thank you so very much! The code worked perfectly! I appreciate the instruction so very much!!!
  • To post as a guest, your comment is unpublished.
    Gary · 1 months ago
    Hello, this worked fine at first, but now I see that not all rows from all worksheets are appended into the combined sheet. Please help!
  • To post as a guest, your comment is unpublished.
    Suzana · 1 months ago
    Oh dear! Thank you so MUCH. Worked PERFECTLY with the Code. Had trouble and was thinking how to resolve this and then...just in a second! Thank you a lot. God bless you!
  • To post as a guest, your comment is unpublished.
    adj · 1 months ago
    This code works for the most part - however, the first line of each sheet (except for the first sheet) is not extracted into the combined sheet. Which piece of the code should be modified to ensure extraction of the first line?