Note: The other languages of the website are Google-translated. Back to English

How to merge or consolidate worksheets or workbooks into one worksheet?

In our daily work, we may encounter a problem that merge hundreds of sheets or workbooks into one sheet for analyzing data which takes a large of time if you use the Copy and Paste command in Excel. Here in this tutorial, I will provide some tips for quickly solving this job.

doc combine 1

QUICK NAVIGATION

Merging all sheets of active workbook into one sheet with VBA
Merging dozens of sheets or workbooks into one worksheet/workbook with clicks
Merging two tables into one and update by a column with clicks
Download sample file


Merging all sheets of active workbook into one sheet with VBA

In this section, I provide a VBA code which will create a new sheet to collect all sheets of the active workbook while you running it.

1. Activate the workbook you want to combine its all sheets, then press + keys to open Microsoft Visual Basic for Applications window.

2. In popping window, click Insert > Module to create a new Module script.

3. Copy below code and paste them to the script.

Sub Combine()
'UpdatebyExtendoffice
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
doc combine 2

4. Press F5 key, then all data across sheets have been merged in to a new sheet named Combined which is placed in the front of all sheets.


ot move

Do You Want To Have A Pay Raise and Much Time To Accompany With Family?

Office Tab Enhances Your Efficiency By 50% In Microsoft Office Working Right Now

Unbelievable, working at two or more documents is easier and faster than working at one.

Compareed with well-known browsers, the tabbed tool in Office Tab is more powerful and more efficient.

Reduce hundreds of mouse-clicks and keyboard typing every day for you, say goodbye to the mouse hand now.

If you usually work at multiple documents, Office Tab will be a great time-saver for you.

30-day free trial, no credit card required.

Read MoreFree Download Now


Merging dozens of sheets or workbooks into one worksheet/workbook with clicks

With the VBA, you only can combine sheets in the active workbook, but how can you merge sheets across workbooks to a sheet or workbook?

Merge sheets across workbooks into one sheet
doc combine 18

Merge sheets across workbooks into one workbook
doc combine 3

For solving this job and satisfy other requirements on sheets-combination, the Combine function has been developed with four combination scenarios:

  • Combine multiple sheets or workbooks into one sheet
  • Combine multiple sheets or workbooks into one workbook
  • Combine same name sheets into one sheet
  • Consolidate values across sheets or workbooks into one sheet

Here takes the second option as instance:

Combine multiple sheets or workbooks into one workbook

After free installing Kutools for Excel, please do as below:

1. Activate Excel, click Kutools Plus > Combine, a dialog pops out to remind you the workbooks you want to combine needed be closed. Click OK to continue.
doc combine 4
doc combine 5

2. In the Combine Worksheets step 1 dialog, check Combine multiple worksheets from workbooks into one workbook option. Click Next to go to next step of wizard.
doc combine 6

3. Click Add > File or Folder to add the workbooks you want to combine to the Workbook list pane, then you can specify which worksheet will be joined together by checking names in Worksheet list pane. Click Next to go to the last step of wizard.
doc combine 7

4. In this step, specify the settings as you need. Then click Finish.
doc combine 8

5. A window pops out for you selecting a folder to place the combined workbook, then click Save.
doc combine 9

Now the workbooks have been merged into one workbook. And at the front of all sheets, a master sheet named Kutools for Excel is also created which lists some information about the sheets and links for each sheet.
doc combine 10

Demo: Combine sheets/workbooks into one sheet or workbook

Kutools for Excel:200 + useful handy tools, simplifying the complicated tasks in Excel into a few clicks.

Say Goodbye To Mouse Hand and Cervical Spondylosis Now

300 advanced tools of Kutools for Excel solve 80% Excel tasks in seconds, pull you out of the thousands of mouse-clicks.

Easily deal with 1500 working scenarios, no need to waste time for searching solutions, have much time to enjoy your life.

Improve 80% productivity for 110000+ highly effective people every day, of course including you.

No longer to be tormented by painful formulas and VBA, give your brain a rest and joyful working mood.

30-day free trial with full features, 30-day money back without reasons.

A Better Body Creates A Better Life.


Merging two tables into one and update by a column with clicks

If you want to merge two tables into one and update data based on a column as below screenshot shown, you can try the Tables Merge utility of Kutools for Excel.
doc combine 11

Kutools for Excel: more than 200 handy Excel add-ins to simplify complicated tasks into a few clicks in Excel

After free installing Kutools for Excel, please do as below:

1. Click Kutools Plus > Tables Merge to enable Tables Merge wizard.
doc combine 12

2. In the step 1 of the wizard, you need to separately select the main table and lookup table ranges. Then click Next.
doc combine 13

3. Check the key column you want to update data in the main table based on. Click Next.
doc combine 14

4. Then check the columns in main table you want to update the data based on lookup table. Click Next.
doc combine 15

5. In the step 4 of the wizard, check the columns you want to add from lookup table to the main table. Click Next.
doc combine 16

6. In the last step of the wizard, specify the setting options as you need. Then click Finish.
doc combine 17

Now the main table has been updated the data and add new data based on the lookup table.

Demo: Combine sheets/workbooks into one sheet or workbook

Kutools for Excel:200 + useful handy tools, simplifying the complicated tasks in Excel into a few clicks.


Download Sample File

sample


Recommended Productivity Tools

Office Tab - Tabbed Browsing, Editing, Managing Of Documents In Microsoft Office 2019 - 2003 And Office 365


office tab

A Professional Add-in for Accelerating Excel 2019-2007, shrink hours tasks to seconds

This add-in includes dozens of professional groups, with 300+ options will automate most of your daily tasks in Excel, and increase your productivity at least by 50%. Such as groups of one-click optons and batch conversions.
Now it's your chance to speed up yourself with Kutools for Excel!


Comments (168)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
used your first Macro function. WOW! thanks for posting
Jason Mills
This comment was minimized by the moderator on the site
I think the code 'Selection.Offset(0, 0).Resize(Selection.Rows.Count - 1).Select' has not to be there. On my end, what it does is select the second row onwards missing the first.
Edwin M
This comment was minimized by the moderator on the site
I have the same problem. It's omitting the first row from the second worksheet added.
Razina K
This comment was minimized by the moderator on the site
Eliminates headers so you don't have duplicated headers
KJ
This comment was minimized by the moderator on the site
Works like a charm, thanks for sharing!
Shannon
This comment was minimized by the moderator on the site
Thank you! The macro worked perfectly.
Daniel
This comment was minimized by the moderator on the site
i dont know, how to use the VBN function in excel, can you please help how to do that.
M. Anandhi
This comment was minimized by the moderator on the site
Awesome, Thanks!!! Very Very Usefull
David Tkemaladze
This comment was minimized by the moderator on the site
Macro one works fine. Thanks!! Its Awesome!!!! :)
Yogesh
This comment was minimized by the moderator on the site
Note this is limited by the 65k row limit, if you need to up the line Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2) range to resolve the problem.
randy
This comment was minimized by the moderator on the site
Great tool however, it does not combine all 21 sheets. How can I expand the range?
Craig
This comment was minimized by the moderator on the site
NOTE!!! You need to be careful with the macro.. it will work if your data has a continuous flow of information and no blank row. this what the line "Selection.CurrentRegion.Select" does. For example, if you need to copy ALL the data on a spreadsheet but there is a blank line at (let's say) row 200, the macro will only copy down to row 200; IT WILL DISREGARD ALL DATA BELOW THAT ROW!!!
taffyJ
This comment was minimized by the moderator on the site
Does anyone knows a command to avoid this issue?? The problem with Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select can be solved? Selection.Rows.Count - 1) is the command originating the issue. Anyone??
Carlos
This comment was minimized by the moderator on the site
Instead of starting from the top and going down, start from the bottom and go up
Ed
This comment was minimized by the moderator on the site
Does anyone knows the code to solve problems of empty rows and columns?. I have it in my data but there is no simple solution as starting bottom up? Thanks!
arno
This comment was minimized by the moderator on the site
For deleting rows that are empty: Option 1: Select cells in column A that need to be deleted. Use Ctrl - and then select Entire Row in popu up. Save workbook and exit. Then open workbook. Option 2: Select rows that need to be deleted. Use Ctrl -. Save workbook and exit The open workbook. Gave option 1 so it was visible to you what was happening. For deleting columns that are empty: Select column. Ctrl -. Save workbook and exit The open workbook.
Diane
This comment was minimized by the moderator on the site
Do we have any solution to copy the data below the blank cell?
arul kumar
This comment was minimized by the moderator on the site
Thank you ! My 2nd row was empty in all sheets & I was confused.
Eyan
This comment was minimized by the moderator on the site
the vba code worked great but I need the combined worksheet to update as I add new rows or new data to the cells in the worksheets that I am combining. Is there a way to do that? Thank you for your help.
Liz
This comment was minimized by the moderator on the site
The macro code worked great for the first combined worksheet, but I need the combined worksheet to update as I add data to the existing worksheets that I am combining. Is there a way to do that?
Liz
This comment was minimized by the moderator on the site
Did you ever figure out how to get it to update in the master as you add in rows to the other worksheets?
MALLORY ANDERSON
This comment was minimized by the moderator on the site
Excellent work.. This has been reduced my work from 2 hours to 10 minutes.
Anto
This comment was minimized by the moderator on the site
Excellent, I used both first macro and Kutools, both has reduced my data gathering and compilation work tremendously. Thanks for sharing.
Idris Runijawala
This comment was minimized by the moderator on the site
I just erased the part given below and now it only updates. Hope this helps... Sheets(1).Select Worksheets.Add
Alper
This comment was minimized by the moderator on the site
Code works great! I need the macro to copy all data on the sheets. It is cutting off the first row after Sheet 1. Only sheet one has the header row, the other sheets are just data. Is this possible?
Angela Fish
This comment was minimized by the moderator on the site
I have question about "Merge all worksheets into one worksheet with VBA code" :The code work very well, but only when it is empty, it adds the header, it is possible to modify the code to say not to consolidate the empty sheet ?
Adnan
This comment was minimized by the moderator on the site
Thanks you saved me an hour of copy and paste :D
Driehlax
This comment was minimized by the moderator on the site
Have two workbooks, same data but second one has extra data and some data has been updated, inv# Status 123 7/2 open inv# status 123 7/2 open 7/7 closed how to merge into one removing or highlighting duplicates and adding any new inv# not in the first one
Debbie
This comment was minimized by the moderator on the site
Works great if your Column A is non-=blank. On sheets with column A blank, it seems to be overwriting. In my dtaa column E is a non blank on all sheets. How can I modify the Copy Destination to account for blank column A on some sheets?
Jogan
This comment was minimized by the moderator on the site
Sub CopySheet() ' ' CopySheet Macro ' ' Dim i As Integer For i = 2 To 25 Sheets("Sheet1").Select Range("A1").Select Selection.End(xlDown).Select Range("A" & CStr(Selection.Row) + 1).Select Sheets("Sheet" & CStr(i)).Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Cut Sheets("Sheet1").Select ActiveSheet.Paste Next End Sub
clopez
This comment was minimized by the moderator on the site
Range("A:A").SpecialCells(xlCellTypeLastCell).Select ActiveCell.Offset(1, 0).Select 'use this code to select last cell in column "A" [quote] Sub CopySheet() ' ' CopySheet Macro ' ' Dim i As Integer For i = 2 To 25 Sheets("Sheet1").Select Range("A1").Select Selection.End(xlDown).Select Range("A" & CStr(Selection.Row) + 1).Select Sheets("Sheet" & CStr(i)).Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Cut Sheets("Sheet1").Select ActiveSheet.Paste Next End SubBy clopez[/quote]
RamN
This comment was minimized by the moderator on the site
MACRO CODE WORKED SO FINE... FAB JOB :-)
Jene
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations