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.
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.
|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.
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:
2. Click OK to go on, 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, 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 then you can open the combined workbook directly by clicking Yes.
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:
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
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 days agoYou rock guys!
To post as a guest, your comment is unpublished.· 8 days agoYou guys are the best!!! You totally saved me and I look like a super star
To post as a guest, your comment is unpublished.· 14 days agoTHANK YOU SO MUCH!!!!!
To post as a guest, your comment is unpublished.· 1 months agoHow to merge multiple workbooks with multiple worksheets into one workbook?
To post as a guest, your comment is unpublished.· 1 months ago[quote name="John"], how to merge multiple workbooks with multiple worksheets into one workbook? appriciate if you could reply.
- ← Previous
- Next →