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 months agoHi.. 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.· 2 months agohello everyone
I want to merged more workbook data in one so give me suggest
To post as a guest, your comment is unpublished.· 2 months agoHi 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!
To post as a guest, your comment is unpublished.· 2 months agoThe VBA code works fine but I want the data to be merged horizontally(side by side).
Hope you can help.
To post as a guest, your comment is unpublished.· 3 months agoTHANK YOU for the VB Code. Amazing. Worked perfectly. I had 72 worksheets to combine into one. It took a second and it was done. YOU ROCK.
- ← Previous
- Next →