Skip to main content

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

Author: Xiaoyang Last Modified: 2020-04-21

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
Hi, I have a system that generates an excel file everyday. The daily file name is of format "<filename-12282021>". The 12282021 is the timestamp and it changes everyday creating a new excel file each day. I want to create a Master Workbook and then append the contents of the daily excel file into that master workbook. How can I automate it so it happens by itself everyday and the master file is update with contents of the daily file.   Thank you.
This comment was minimized by the moderator on the site
Hi rd2022, sorry that I have no idea to solve your problem so far. You can go to our forum https://www.extendoffice.com/forum/excel.html to leave this message, maybe some users have the same problem and has been solved.
This comment was minimized by the moderator on the site
In combining shhets I want to add one extra column in combined sheet as ShhetName
Can you suggest ?
This comment was minimized by the moderator on the site
The Combine function of Kutools for Excel can help you to insert sheet name in a new row of each combined range in the combined sheet by checking First row of each range (new row) in Insert worksheet information section in the last step of the Combine.
This comment was minimized by the moderator on the site
Hi - this function works well except my file has more than 900 sheets, and when I change the maximum number of rows (Loc 16, "A65536") to a higher number, the macro doesn't work. Is there a way to increase the number of rows that can be shown on the combine sheet?
This comment was minimized by the moderator on the site
The VBA code works fine but I want the data to be merged horizontally(side by side).
Hope you can help.
This comment was minimized by the moderator on the site
Hi,


Great Code and for the most part it works fantastically well.


Could I suggest creating a written tutorial for each line of code. I'd love to be able to play around with it to suit my Company's needs.

In the meantime however could you talk me through problems that would hinder this code from copying all data from each Sheet? I've noticed some sheets in my workbook aren't being copied at all.

To give you some context I'm dealing with roughly 330 sheets and they hold from 50 to 500 lines in each.


looking forward to hearing from you.
This comment was minimized by the moderator on the site
Hi, Daniel, I do not understand your question as you describe it not clearly, but if you have the Combine function in Excel, it can solve most of your question about combining sheets.
This comment was minimized by the moderator on the site
Hi,

Is it possible to only copy a particular range of cells for example A:4:FU38 from each sheet?


Also in the Combined sheet where the data is copied to, can the tab names from where the data is copied included in column A to enable lookups or to pivot the data?


Finally, can i specify the list of worksheets from which to copy the data across or maybe i can specify something like copy all worksheets to the left of a particular worksheet in the file.


Thanks in advance for your help
This comment was minimized by the moderator on the site
Hi, HS, you mean to combine same ranges from sheets into one sheet, it that right? If so, this article may help you: https://www.extendoffice.com/documents/excel/2754-excel-combine-ranges-from-different-worksheets-into-one.html#a3
This comment was minimized by the moderator on the site
This VBA code is an absolute lifesaver. Thank you!!
This comment was minimized by the moderator on the site
Hi. This code works well but I have the same problem as adj. The 1st row form each tab doesn't appear in the combined tab - except he first tab. Thanks
This comment was minimized by the moderator on the site
Just delete "Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select" from the code.

If you want to merge all rows from all worksheets, just use the code:

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.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Next

End Sub
This comment was minimized by the moderator on the site
Yes, the code only can keep first row of the first tab, because it is used to consolidate, if you want to keep all contents of each sheet, you can try Combine utility of Kutools for Excel, it is free fior 60 days
This comment was minimized by the moderator on the site
i had a problem when I try to combine all same name worksheet to one worksheet, it works but some value are missing. so what should i do?
This comment was minimized by the moderator on the site
Hello, elok, did the problem appear while you applying Combine function? If so, please go to contact us to describe the problem with more details, our support will handle the problem for you.
This comment was minimized by the moderator on the site
Thanks that macro saved my day. I had over 40 sheets of data to combine into one and although my computer bogged for a minute or two, it completed without issue. The only modification I made was I tweaked the macro to start with A2 instead of A1 since A1 was my column headers.
This comment was minimized by the moderator on the site
Thanks for your support, Andrew, I am glad that the macro can help you.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations