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.
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!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
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 Kutools Plus > 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 merge multiple worksheets into one workbook, you can apply check Combine multiple worksheets from workbooks into one worksheet option in Combine Worksheets step one.
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.
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.
2. In the Tables Merge first 1 step dialog, select the lookup table, table2. If the tables have headers, check the header options.
3. Click Next, select the column you want to update data in table1 based on.
4. In Tables Merge step 3, select the columns in main table (table1) that you want to update data.
5. Click Next, select the column you want to add to main table (table1) based on lookup table (table2).
6. In last step, you can choose the options to operate the merge result.
7. Click Finish, now the main table has been updated and added based on lookup table.
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.· 4 years agoWorks like a charm, thanks for sharing!
To post as a guest, your comment is unpublished.· 4 years agoused your first Macro function. WOW! thanks for posting
To post as a guest, your comment is unpublished.· 4 years agoI 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.
- ← Previous
- Next →