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!


Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    VJ · 3 years ago
    How can I modify the code to paste as value only?
  • To post as a guest, your comment is unpublished.
    Mike · 4 years ago
    This isn't a merge...it's simply stacking data. Change the title, please.
  • To post as a guest, your comment is unpublished.
    Maher · 4 years ago
    Thank, I used Kutools, it is so easy to use. Thanks again!
  • To post as a guest, your comment is unpublished.
    Eduardo · 4 years ago
    Used your first Macro and worked! Great work, thanks a lot!

    Kind regards
  • To post as a guest, your comment is unpublished.
    Shireen · 4 years ago
    My scenario - I require to be able to refresh the data from the source data also:

    2 worksheets in two different workbooks - the same formatting
    I need to bring the data across from both worksheets into one worksheet - in a separate workbook
    As the data is added to and changed daily I would need to refresh this data - this also means that the range in these worksheets would also change if rows are added.
    These sheets are also read only when being worked on by the User, so I would need to refresh each time the data is saved.
  • To post as a guest, your comment is unpublished.
    Shireen · 4 years ago
    My scenario.......but I need the function to refresh data as both sheets are added to and info is updated daily (so the ranges would also increase).

    2 worksheets in two different workbooks
    Data is formatted exactly the same in both sheets

    I want to consolidate these sheets into one sheet (and keep the formulas), but as the data changes daily I would need to be able to refresh the data from the source documents.

    Can you assist?
  • To post as a guest, your comment is unpublished.
    Kiran Kanth · 4 years ago
    Hi,

    thanks a lot this saved my time in consolidating 80 to 100 sheets in one work book.
    thanks once again


    Kiran Kanth
  • To post as a guest, your comment is unpublished.
    Saleem Ansari · 4 years ago
    Hi Daniel,

    thank for your support & information but my problem is still not resolve.

    i have four sheet in one workbook with the name of (Reason Code), (Offline Code), (Work File 1) and (Work File 2).

    i want to merge the data of only between two sheet (Work File 1) and (Work File 2) but it should not create any new sheet, data should be merge in (work file 2) sheet from work file 1
  • To post as a guest, your comment is unpublished.
    Ray and Eric · 4 years ago
    Thank you for the code! We had to copy 200 sheets into one, just one comments regarding the range; the maximum row/range is
    1,048,576 rows by 16,384 columns
  • To post as a guest, your comment is unpublished.
    Daniel · 4 years ago
    How do you omit a sheet?

    I have 3 sheets(Tracker)(Archive)(Note Options). I want to combine only (Tracker)(Archive)sheets to a master sheet.

    I am also trying to find a code that will auto refresh Combined sheet when new information is listed on (Tracker)(Archive)sheets .

    Please help if possible. Code Sample listed below.

    Sub Combine()
    Dim J As Integer
    Dim wrk As Workbook
    On Error Resume Next

    Set wrk = ActiveWorkbook

    For Each sht In wrk.Worksheets
    If sht.Name = "Master" Then
    MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
    "Please remove or rename this worksheet since 'Master' would be" & _
    "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
    Exit Sub
    End If
    Next sht

    'We don't want screen updating
    Application.ScreenUpdating = False


    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Master"

    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)


    Application.ScreenUpdating = True
    Next

    End Sub

    Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("a:l")) Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Call TrapTest

    Application.EnableEvents = True

    End Sub
  • To post as a guest, your comment is unpublished.
    Saleem Ansari · 4 years ago
    hi.

    i want to merge the data only two sheet. but it should not create in new sheet.

    it should be merge from sheet 1 to sheet 2

    please help.
  • To post as a guest, your comment is unpublished.
    steve · 4 years ago
    you rock, thanks a bunch....
  • To post as a guest, your comment is unpublished.
    Antonia · 4 years ago
    The VBA code worked great! If I update one of the worksheets (i.e. enter more data) will that data automatically be entered into my new 'Combined' worksheet?
  • To post as a guest, your comment is unpublished.
    Guzman · 4 years ago
    Hi ! Thanks for the code.
    About -[b]"Merge all worksheets of active workbook into one worksheet with VBA code"[/b]-

    Is it possible to do it with a For Each construct to navigate through the Sheets?
    Thanks.
  • To post as a guest, your comment is unpublished.
    Janett · 4 years ago
    Thank you, this has helped me a lot!
  • To post as a guest, your comment is unpublished.
    Nat · 4 years ago
    When using the VBA providied above, is there a way for the combined sheet to automatically update when the sheets that is combined are added to it, so it is always running?

    Thanks!
  • To post as a guest, your comment is unpublished.
    ronald bthakur · 4 years ago
    Hello Everyone,
    This is awesome to merge the multiple worksheet into one worksheet via vba coding or macros, but can anyone help me to merge data from multiple worksheet in to one blank worksheet using excel functions.
    Thanks
  • To post as a guest, your comment is unpublished.
    ronald bthakur · 4 years ago
    this is awesome to merge the multiple spreadsheets using macro, but can anyone help if i can do the same process using excel functions....because i do not know the vba coding....
  • To post as a guest, your comment is unpublished.
    Gabriel · 4 years ago
    Thank you!
    The code worked perfectly!
  • To post as a guest, your comment is unpublished.
    Andre · 4 years ago
    Thanks a lot for the combine code
  • To post as a guest, your comment is unpublished.
    Trey · 4 years ago
    I have 12 worksheets (1 for each month of 2015), running Excel 2010. Each worksheet has approximately 45k rows. When I run the macro it copies about 6 of the the 12. There should be plenty of rows. If I start from scratch and run the VBA again, it still only combines the same month/worksheets? I checked my headers and I think they all match, but maybe that could be the issue?
  • To post as a guest, your comment is unpublished.
    dan · 4 years ago
    Does anyone know of a way to have the combined worksheet to ouput the actual data values only from the other sheets, as opposed to formulas? The formulas don't translate to the combined page.
  • To post as a guest, your comment is unpublished.
    Somasekhara G · 4 years ago
    Thank you so much the initial code is worked for me. But i need to know that, i have some blank rows in the middle, but i need to copy the entire data. would like to know how to modify this. Thanks again.
  • To post as a guest, your comment is unpublished.
    Dhanapal · 4 years ago
    Wow very nice tool to merge multiple sheets....Superb
  • To post as a guest, your comment is unpublished.
    Ravi · 4 years ago
    Thank's Brother I have visited many times on this page but i couldn't recognised now finally I find it. Thanks ;-)
  • To post as a guest, your comment is unpublished.
    Maurice · 4 years ago
    In lieu of a donate button I'm gonna get kutools for my personal computer. This little macro saved me and my 20 coworkers from having to enter more than 9,200 accounting entries for our payroll. Thanks, guys!
  • To post as a guest, your comment is unpublished.
    Clive · 4 years ago
    Can't get the macro to work in excel 2013. But I think its because I am trying to combine 18 sheets in 1 workbook.
  • To post as a guest, your comment is unpublished.
    kamal · 4 years ago
    when i run the code it only merges 3 sheets only out of total 20 sheets.any body please help
  • To post as a guest, your comment is unpublished.
    yathi · 5 years ago
    hi thanks for the code,

    it works great. .

    can you plz help me in getting the respective worksheet names in front of the data which are combined.

    i.e if i am combining 2 columns of data from 20 worksheets, i want to get one more column of data which gives name of the sheet that particular data is fetched from.

    thanks in advance

    Yathish
    • To post as a guest, your comment is unpublished.
      Vijay Dilli · 4 years ago
      I have this need as well to have an additional column with the worksheet name as 1st column. Is this possible? I am not very familiar with VBA and I am not sure I can make these changes myself with out impacting the existing results.

      Vijay
  • To post as a guest, your comment is unpublished.
    Mikael Hjelm · 5 years ago
    Thanks for the macro. Now I can use it with empty rows.
    I add an extra kolumn A (with all tabs marked) and fill it down with any letter as deep the largest table have rows.
    Well I did not check just estimated and used advsnced search with only unique values selected, and over the whole cell area, to remove the empty rows that comes in between in the Combine tab.
  • To post as a guest, your comment is unpublished.
    Elahi baksh · 5 years ago
    Thank u very much this program reduces my efforts of doing copy paste job
  • To post as a guest, your comment is unpublished.
    Peter · 5 years ago
    Is there a solution to merge 6 tables into one row, please?

    It`s hard to describe, but I have converted a pdf into excel and it simply pasted the date into separate sheets, means every 6 sheet would be one row and I have around 6136 pages...

    Thanks,
    Peter
  • To post as a guest, your comment is unpublished.
    Rodrigo · 5 years ago
    you saved my lyfe...tks
  • To post as a guest, your comment is unpublished.
    Leslie · 5 years ago
    Great, worked perfectly!
  • To post as a guest, your comment is unpublished.
    Omar · 5 years ago
    Thanks a million :-)
  • To post as a guest, your comment is unpublished.
    Subhashini K · 5 years ago
    I downloaded the tool but it is not working. I want to combine multiple sheets. I open the requisite excel sheets and then as soon as I click on enterprise and then combine, all the sheets disappear. Nothing happens and if I try to open them again I get the alert that the sheets are locked by another user. I have to restart my system to again work on them. Can you please suggest a solution for this?
  • To post as a guest, your comment is unpublished.
    Searcher · 5 years ago
    The code works great, but if the macro is not resident in the Active worksheet, if works on the wrong worksheet. I adapted it it to work with the Active workbook as follows:

    Sub Combine()
    Dim J As Integer

    On Error Resume Next

    With ActiveWorkbook

    .Sheets(1).Select

    .Worksheets.Add ' crteate a new worlsheet; it becomes the new SHEETS(1)
    .Sheets(1).Name = "Combined" ' Call it 'COmbined'. Hopefully there is no conflict!
    .Sheets(2).Activate ' go to the first user's worksheet
    Range("A1").EntireRow.Select ' Select Row 1
    Selection.Copy Destination:=.Sheets(1).Range("A1") '
    For J = 2 To .Sheets.Count ' these are the User's original worksheets
    .Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' adjust selection by removing Row 1
    Selection.Copy Destination:=.Sheets(1).Range("A65536").End(xlUp)(2) ' Assumes Column A always has data
    Next ' worksheet

    End With

    End Sub
    • To post as a guest, your comment is unpublished.
      sung · 4 years ago
      Great, I didn't need this code. but the code listed seems not perfect, so started read and this seems great!!..

      I know i might need the code soon or later so i'm taking it

      [quote name="Searcher"]The code works great, but if the macro is not resident in the Active worksheet, if works on the wrong worksheet. I adapted it it to work with the Active workbook as follows:

      Sub Combine()
      Dim J As Integer

      On Error Resume Next

      With ActiveWorkbook

      .Sheets(1).Select

      .Worksheets.Add ' crteate a new worlsheet; it becomes the new SHEETS(1)
      .Sheets(1).Name = "Combined" ' Call it 'COmbined'. Hopefully there is no conflict!
      .Sheets(2).Activate ' go to the first user's worksheet
      Range("A1").EntireRow.Select ' Select Row 1
      Selection.Copy Destination:=.Sheets(1).Range("A1") '
      For J = 2 To .Sheets.Count ' these are the User's original worksheets
      .Sheets(J).Activate
      Range("A1").Select
      Selection.CurrentRegion.Select
      Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' adjust selection by removing Row 1
      Selection.Copy Destination:=.Sheets(1).Range("A65536").End(xlUp)(2) ' Assumes Column A always has data
      Next ' worksheet

      End With

      End Sub[/quote]
  • To post as a guest, your comment is unpublished.
    Derek · 5 years ago
    Thank you for this! With a little tweaking of datasets, this worked like a charm.
  • To post as a guest, your comment is unpublished.
    Melisa · 5 years ago
    I have a workbook with 7 sheets that I would like to combine.

    Each of the sheets is a check list and the completion date is in a different column on each sheet depending on the number of steps so on one sheet it is column H another it is column AR for example.

    I thought that the best way to get around this issue would be to make a named Data set which includes the desired columns as the columns would be in the same order regardless of which sheet it is on.

    Is there any way to modify this code to looks at the dataset on the sheets rather than all active cells? and keeps information in the same columns?
  • To post as a guest, your comment is unpublished.
    Bhumika · 5 years ago
    I just used the Merge Worksheet function. Worked like a charm! Thank you SO much for posting this! It is a life saver. Even the MS office website didn't list this...
  • To post as a guest, your comment is unpublished.
    akmuseums · 5 years ago
    it's only compiling about a 1/4 of my data! how can I get the rest of my sheets into it?

    thank you!
    • To post as a guest, your comment is unpublished.
      kamal · 4 years ago
      same problem here...i have 20 sheets but it only combines 3 sheets
  • To post as a guest, your comment is unpublished.
    maryam · 5 years ago
    hello
    thanks alot for your merge program.you help me that my boss be happy :lol:
  • To post as a guest, your comment is unpublished.
    Mary · 5 years ago
    This is perfect for what I need. Thank you.

    How can I tell it to not copy the headings on each tab, just the data. I'm ending up with headings in my combine tab 40 times...

    Sorry if this posts twice. I'm not seeing it in the refreshed comments...
  • To post as a guest, your comment is unpublished.
    Mary · 5 years ago
    This is exactly what I need. One issue: I don't want the headings from each tab, just the data. How do i tell it to not copy all the headings?

    Thank you.
  • To post as a guest, your comment is unpublished.
    Chris · 5 years ago
    Life saver. Thank you.
  • To post as a guest, your comment is unpublished.
    cherry codilla · 5 years ago
    please help me i cant find the merge worksheet. where i can find it?

    thank you,
    cherry
  • To post as a guest, your comment is unpublished.
    Kedar · 5 years ago
    At this moment I have got smth like this:

    Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(6).Activate
    Range("A1:A3").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    For J = 1 To 5
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(3, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(6).Range("A65536").End(xlUp)(2)
    Sheets(6).Select
    Next
    End Sub

    Everything works just great. The only thing I want to do is to add some automatic refresh for VBA rule. I want to add new content to other Worksheets and see results at last one with VBA.
    • To post as a guest, your comment is unpublished.
      Antonia · 4 years ago
      Did you ever figure out how to do this?
  • To post as a guest, your comment is unpublished.
    Kedar · 5 years ago
    I have one simple question (I'm not a VB programmer).

    Please tell me how to modify that rule to paste the results inside already created worksheet (let's say "COMBINED") and not create new one?
  • To post as a guest, your comment is unpublished.
    kristin · 5 years ago
    please help me, and you did. I am so thankful, in life for the work experience, and even though I did the prints, I got a good , or even better a even review. I love my new job.
  • To post as a guest, your comment is unpublished.
    Chief · 5 years ago
    Amazing solution. how can you modify the code to work on cells that are protected?