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. Download Now
Recommended Productivity Tools
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:
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.
(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.
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.
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.
After installing Kutools for Excel, please do as following steps:
1. Click Enterprise > Combine, see screenshot:
2. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one worksheet option. See screenshot:
3. And click Next button, on step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, if you want to add other workbooks or folders, you can 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 button to select the range that you want to use. (By default, the used range is selected for each worksheet) See screenshot:
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:
5. After finishing the settings, click Finish button to combine the worksheets. And you will be asked if you want to save this scenario, see screenshot:
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.
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:
If you want to know more about this feature, please click Combine multiple worksheets from workbooks into one worksheet
Free Download Kutools for Excel Now.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...