Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Condensing Multiple Worksheets Into One.

Condensing Multiple Worksheets Into One

by Allen Wyatt
(last updated December 3, 2015)


If you get workbooks that have identically structured data on each worksheet, you may be interested in a way to combine the multiple worksheets into a single, large worksheet.

The concept behind doing the condensation is rather easy: You simply need to copy the data from the second and subsequent worksheets to the first empty row on the first worksheet. Fortunately, Excel includes a feature that allows you to do this very process—the Consolidate tool.

The Consolidate tool allows you to combine worksheets where data is defined by position or by category. By position means that the data is in the same position on every worksheet. For instance, if the data tables on each worksheet have the exact same columns, then you would consolidate by position. By category means that you want to combine data from tables in which the data may not use a consistent structure. You use this type of consolidation if the columns in the data tables are in different orders.

In the workbook whose worksheets you want to consolidate, choose Data | Consolidate. Excel displays the Consolidate dialog box. (See Figure 1.) There are many controls in the dialog box, but the primary thing you need to worry about is specifying the ranges to consolidate.

Figure 1. The Consolidate dialog box.

You specify ranges by using the Reference box. Specify in the box the first range you want to consolidate. If you are consolidating by position, then the reference should not contain any column labels; if by category, then you should. When you specify the range reference, you click Add, and the reference appears in the All References list. You continue to define reference ranges until they are all complete.

If you want the consolidated data to contain links to the original data, then make sure the Create Links to Source Data check box is selected, otherwise clear it. You can then click OK to do the consolidation.

Note that there are other controls in the Consolidate dialog box; the controls mentioned above are the ones you should pay attention to at a minimum. The best way to find out what the others do is to play around with them, doing a few consolidations.

If you prefer to not use the Consolidate tool, you can easily create a macro that will do the consolidation for you—provided the structure of each worksheet is identical. The following macro steps through all the worksheets and combines the data to a new worksheet it adds at the beginning of the workbook.

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
End Sub

When the macro is done, the first sheet in the workbook, named Combined, has all the data from the other worksheets. The other worksheets remain unchanged.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3005) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Condensing Multiple Worksheets Into One.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Hyperlinks Not Found

When creating hyperlinks in a document, it is important to remember the difference between absolute and relative ...

Discover More

Crashing when Searching

If you use Excel's Open dialog box to search for files and you notice that doing so ends up crashing your system, you may ...

Discover More

Combining Numbers and Text in a Cell

There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

Combining Worksheets from Many Workbooks

Do you need to pull a particular worksheet out of a group of workbooks and combine those worksheets into a different ...

Discover More

Switching Headers in a Frozen Row

Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...

Discover More

Viewing Same Cells on Different Worksheets

When switching from one worksheet to another, you might want to view the same portion of the new worksheet that you were ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one less than 3?

2018-12-08 05:40:41

Alan Elston

Hi Hannu This sounds on the face of it something that VBA can do quite easily. It is difficult in the confines of a comment section to pass the info across, or for you to give enough information about your requirement. I suggest asking here , for example is more space to get your information across there, and you can upload a sample file or two. If you give a file or two then make sure you desensitize and reduce the data: Make names up etc. and just include enough data to demonstrate what you want done. Try to include before and after worksheets: Before: what you have, and thenAfter: Fill in samples manually of what you would like the code to do for you, based on your Before.Keep the test data to a minimum, but choose it carefully such that it demonstrate all possible scenarios Alan ElstonDoc.AElsteinDocAElstein ( excelfox )

2018-12-07 15:08:06


I am having large amount of data. I have students marks of the whole school in such a manner that one folder is created as science with in this folder i have all the excel marklists of all the grades and there sections as well. Ex science folder > 1-A , 1-B 1-C ........ 7-A etc. This way i have all other subjects folders . In the same order. Each marklist has quiz marks , exam marks , behaviour marks etc and its total. I have to make a master mark list for each class by clubbing all the subjects total marks. Ex. 1-A file with science total , math, social , english marks in there respective columns. Is it possible to automate this process rather tgan opening each file copy anf paste in the respective column in master workbook? Please help!

2018-10-10 12:27:41


Hi, I need to find a way to do this:

I have 26 Excel files (let's name them A to Z) with the same formatting, and one file named "Compiled Files"

I need to copy the merged cell "L1:S1" in the Calculations tab/spreadsheet of files A to Z, and paste them into "Compiled Files"

I also need to copy the cells for C21 to K36 in the Calculations tab/spreadsheet of files A to Z, and paste them beside the pasted "L1:S1" values in "Compiled Files".


2018-09-03 21:58:06


Great Macro. This works perfect and it is simple. Thanks so much!!!

2018-07-19 17:26:49


You are Am-Maz-Za-Zing!

I save so many of your tips in my Favorites that I finally subscribed and added your Home Site to my Favorites Bar.

Thank you for making Learning Excel FUN & PAINLESS!!!!

2017-09-19 16:50:45


Hi Alan,

I've been looking for a solution on a quite a few different forums but haven't found any.
What I'm trying to do is:

I have a workbook which has 8 worksheets. each has same columns but different data. I want to select some columns( A, G, L T) from each worksheet
and copy all that data in a new worksheet in a different workbook in column A, G, L, T. I don't want to add more columns. Could you please help me with this? so far I've only been able to set up the connection using ADO obj.

appreciate your help!

Thank you

2017-06-06 02:52:48

Alan Elston

P.s. Catch Allen's Post on naming Sheets also, should you want to rename the sheets:

2017-06-06 02:31:56

Alan Elston

@ Shika . - You are welcome. Thanks for the feedback :-) , Alan

2017-06-05 04:09:24


Thank you Alan Elston. This is perfect - Much appreciated :-)

2017-06-03 05:50:08

Alan Elston

Hi Shika
You add a line to assign the name of the worksheet(J) to the next free row column A Range object, like this

Option Explicit
Sub Combine() '
Worksheets.Add ' add a sheet in first place
Worksheets.Item(1).Name = "Combined"
' copy headings assuming they are the same on all Worksheets
Selection.Copy Destination:=Worksheets(1).Range("A1")
' work through sheets
Dim J As Long
For J = 2 To Worksheets.Count ' from sheet 2 to last sheet
Let Worksheets(1).Range("A" & Rows.Count).End(xlUp).Item(2).Value = Worksheets(J).Name 'Put name in next
Worksheets(J).Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' select all lines except title
Selection.Copy Destination:=Worksheets(1).Range("A" & Rows.Count).End(xlUp).Item(2) ' copy cells selected in the new sheet on last line
End Sub

2017-06-02 06:07:17


Hi. How would we update the code to include the name of each of the copied sheets near each row of the copied data? For example if 3 lines were copied from Sheet 1 how would we update the code to include Sheet 1 in Column A before each of the 3 lines of data are copied? Thanks

2017-03-24 12:58:03

dee mcqueens

If there are blank columns in between how do I get it to bring back all columns without skipping blanks?

2017-02-07 12:10:14


I have 5 sheets with donation history for individual years 2012-2016. I need to merge the sheets so that the columns are as follows:

Donor Name Total Gifts 2012 Total Amount 2012 Total Gifts 2013 Total Giving 2013 etc.

So you may have given in 2012, 2014, 2015 but not 2013 and 2016 therefore those would be left blank.

Additionally a name may be on one list but not another, therefore it would be added to the master list.

Is this possible or am I asking way to much?

2017-01-27 05:14:10

Alan Elston

Did you notice at the top and bottom of the Blog:

....... ".....Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions.............."

2017-01-26 09:42:35


I had 67 sheets but couldn't figure out why only a fraction were being combined. The problem is in the code's Destination Range. It's set to "A65536". Since I'm using Excel 2016 which has a larger record limit, I changed it to "A1048576" and that did the trick

2017-01-04 05:25:31


how can i relative address the excel sheet name to a cell like this

2016-12-30 12:15:36


And it worked without any changes even in Excel 2016 for Mac!

2016-12-30 12:14:26


I looked EVERYWHERE for a simple answer to my problem, to simply combine the tables in multiple worksheets (over 130 sheets) within a single workbook into a new sheet within the same workbook. You are da man!!!!!

The macro worked best for me, as I didn't want a sum, avg, etc., of my data, just copying the data while keeping my original into. Only one thing I'd do to make this perfect is to keep the first sheet and place the combined data into another sheet, but I can simply copy that sheet back in from a copy of the original file.

2016-12-21 05:36:36


thank you

working perfect :)

2016-11-20 11:50:38

kadr leyn

Thanks for tutorial and codes.

I made some additions to the codes and added the ability to take the subtotal of the desired column:

It's video:

Application.DisplayAlerts = False
Application.DisplayAlerts = True

Sheets(1).Name = "Grand_Table"
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Last = FindLastRow(Sheets(1))
With Sheets(1).Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
'Application.CutCopyMode = False


Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

2016-11-17 01:16:54

Premkumar John

Can you please help me. I have four sheets in my work book and wish to combine only sheet 1 & 2

Kindly seek your valuable advise

Thank you

2016-11-10 00:19:37


Tried copy/paste the code to VBA but it only consolidates 2 out of 10 worksheets. Please help point out where I'm getting it wrong. Thanks.

2016-10-25 10:52:23


The macro did not work for me. I had an xml export from a database that separated everything into tabs. It had headers that I removed, in case that was the issue. But it's still showing up as "Sheet63" and completely blank. Each tab is formatted the same, but is there a reason why this might not be working?

2016-09-30 16:13:46


I have a MACRO VBA worksheet that i have created to help track different process for jobs i give to contractors. i am trying to put together a report based on the supervisor assigned to the jobs that shows the job number, who its issued to, date started, the status and finally notes. my work sheets are labeled as follows: Ready, Scheduled, InProgress, OnHold, and completed. please help lol

2016-09-15 16:14:41


After I ran the macro I literally said I LOVE YOU!!! out loud. You have saved me so much time. Thank you thank you thank you!

2016-07-25 13:57:21


I have information in A4:V4 on 30 different sheets that I would like listed into one sheet titled "SUMMARY". How do I go about doing that? Thanks in advance.


2016-07-21 13:57:39


Works great, but how can this be macro be setup to automatically update the consolidated page when there is a change on one of the worksheets?

2016-06-26 12:18:31



I am trying to combine multiple spreadsheets using the above code, but when i run it, if it meets a blank row, the code stops. Any tips ?

Thank you

2016-06-11 06:21:23

Dhirendra Kumar

Sub Combine()
Dim J As Integer

On Error Resume Next
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
End Sub
Above code will work only on single worksheet, if I have multiple worksheets then how I can pull data and if I want to pull data from specific cell like A2, B4, D6 & K35. what code need to apply.

Please help me, I am not aware of VB coding.


2016-06-06 14:19:03

Nadine O

Thanks - works great!
I had to delete blank rows at the top of a couple of sheets, otherwise only the header gets copied over.

2016-05-26 08:18:19


How to cosnolidate work sheets. How to enter the data in the reference box correctly.

2016-05-12 12:05:48



Please see below for code I have as a base.

I am trying to copy ALL rows including the header. I tried to remove the offset and change the numbers but no luck.

Any help would be great!

Sub Combine()
Dim J As Integer

On Error Resume Next
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
SSelection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2) ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
End Sub

2016-05-09 11:14:27


Works great, but how can this be macro be setup to automatically update the consolidated page when there is a change on one of the worksheets?

2016-05-04 21:01:48


Thank you for the macro.
It worked beautifully.

2016-04-28 14:16:06


Was able to combine 97 tabs in one. fabulous!

2016-04-28 03:16:57

G Durga REddy

I have an Excel File with 10 Excel Sheet.

I Want to Copy 10 Excel Sheets Date into 1 Excel Sheet (that one is First Excel Sheet)

But this tip in not working

Requesting you to provide the usefull Tip

2016-04-07 05:05:20

suresh kondur

It was owesome. Saved much time in consolidation

2016-04-03 06:15:03


I could able to combine 3 worksheets, which I wanted. You Rock......

2016-03-04 11:53:25


Hi, the macro works very well, I would just know 2 more things:
1. How to get in the new sheet the Column name?
2. How to order the new sheet by column (ex. Column D, Data sort by most recent)

Thank you very much

2016-03-03 15:21:32


I used the macro that did the consolidation of many spreadsheets in many workbooks to make short work on combining several hundred spreadsheets into one. The macro save me a good deal of time. Really appreciate the help.

2016-03-02 13:34:01


I was trying to combine 13 worksheets and used the code but it combined only 2 of the 13. I made sure all row labels are identical and tried several times but unfortunately got the same result.

2016-03-02 12:12:59


This site is incredible. I have often used it to quickly find the solution for many complex operations in Excel and all have worked the first time from the canned Excel tools that are walked through so well to the macros that make the operation run seamlessly. Thanks to Mr Wyatt for all of his shared knowledge, It has saved me weeks if not months of brute force work over the years.

2016-02-19 14:52:51


I used this in Excel 2010 to combine 14 sheets in one workbook. It worked perfectly. First time I have ever copied code from a website and it worked. Thanks.

2016-02-17 23:15:35


Superb... Great.. It works for me.. Thanks a lot..

2016-01-11 18:33:21


I kept on trying to run the codes---

I have three sheets of data, each sheet contains about 3500000 rows.

I have tried couple of the Selection.Copy Destination options but the end result of my combined sheet is always unsatisfactory:

Instead of adding up all the rows of all my spreadsheets, I notice that my combined sheet has the same amount of row count as my first sheet, and that the beginning row and the end row is the same which tells me that the code has failed to copy and paste the other two sheets.

Any idea what the problem could be? and how to fix it?


2016-01-05 11:59:42


Just for reference, if you are using a large data set for this, the formula specified in the last line will cause truncation if you have more than 65,535 total rows, as the formula above seems to be written for older version of Excel.

You may want to try : Selection.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp)(2)

2015-12-16 09:14:32


Fantastic. Just had an idea to automate the data mining. This code had solved 50% time.

2015-12-01 01:18:36

David Caple

I'm sorry to report that the VB code does not work (Excel 2010). When attempting to run it I get a "Compile error: Invalid outside procedure" :(

2015-11-23 21:38:47

Jill Hinson

This works great. And, I need to combine and link the source data. Do you know how to add that to the marcro you gave us?

Thanks so much,

2015-10-29 06:21:08


i have been trying to copy and auto populate entries from multiple tabs to form and a master sheet, which automatically adds any new entries added
the consolidate formula is not copying any information what so ever into the newly formed sheet

2015-10-28 03:22:16


I have similar question as Priya. I want to combine particular rows from each sheet into a single sheet and also keep the names of the sheet to identify which source it is coming from.

2015-10-27 20:37:03


How to exclude some of the worksheets from the formula? And can you ask macro to dump the combined data into a worksheet that is already in the file and not create a 'combined' worksheet everytime to run macros.

2015-10-05 06:14:22


Hi I want to know how do I specify in the macro if I only want just two out of the three worksheets to be combined into one.Is that possible? Thanks!

2015-09-21 14:58:47


Great solution. Thanks for sharing!

2015-09-17 20:06:34


Legend ! Works perfectly - Thank you !!

2015-09-16 07:50:32


I want to copy a particular row number e.g. row no 11, from over 150+ worksheets in a single workbook. workbook names are 101, 102, 103, 104 etc having same format across.
I also want to put together this row number from all worksheets in separate sheet one below the other.
The same goes for any selected column.
Kindly help.
I tried the above code, it copied row no.2.

2015-08-21 19:00:41


Thanks a ton !!!!!

2015-08-03 15:35:53

Lorin Rivers

This also works in the latest Excel for Mac (2016).

If you are having trouble getting this code to work, make sure that in each worksheet there's nothing above the data you want to combine

2015-07-30 13:01:51


Excellent! Worked like a champ with Excel 2013.

Thank you,

2015-07-07 02:04:56

Pawan K Mishra

Hi Guys,

I tried this macro, But in my case only only sheet 1 is coming in combined file, rest are not coming into this

Please Help

2015-06-26 05:03:31


What is the "this" that didn't work for you? There are multiple techniques described in the tip.

2015-06-09 12:08:48

Vijay Menghani

Great Job, it worked first time without any issues and solved my task.

thank you so much..!!

2015-06-09 09:06:54


It worked like magic to an amateur.
It saved a lot of time; I was expected to merge 58 sheets with 'detailed' details. Thank you so so so very much.
God bless you.

2015-05-22 02:23:19


I want to combine different excel sheets data in one folder to on excel sheet.How can i do....assist me how to do...

2015-05-19 15:38:12


Hello, Great work!

But i need only first column and last column of all the worksheets, Combined into 1. And all the worksheets has different number of Columns.

2015-04-09 09:17:14


Great help!
This macro works great for me, thanks much!
What if I want the resulting sheet "Combine" only contains values (not formula?

Much appreciated in advance!

2015-03-21 07:59:38

Revd Stephen Froggatt

Thank you so much, Allen, for this excellent macro. Worked first time and did exactly what I needed.

2015-02-17 11:09:36


I like the macro but I have a similar issue has someone previously. If I use the macro with a file with no appropriate data that I want to copy over(as in I've added a filter and it filters data out), it copies the headings over. Why is this? Is there a way to stop it?

2015-01-09 05:05:16



I really like the code but I need help changing the last part of it. I would like to paste the information as values.
Could you please help?

Many thanks

2015-01-07 09:52:13

Edward K

Tried using this formula and it did produce a new worksheet titled "Combined" but did not populate the sheet with any data. To be fair I am grasping at straws since I have almost no idea what I am looking at in the code here. I am attempting to pull data from about 300 other worksheets together and need to get data from the "A21:J42" range. Each existing worksheet is identical but their is no standard naming convention for the worksheets. Would appreciate any guidance.

2015-01-03 22:21:00


Hi Julie, I found and used some software for a task similar to yours It is called Merge Without Macros and you could use this software to merge the worksheet you just got with your original spreadsheet. Here is the link to download it

2014-12-12 12:58:04


We had 400 members just added to our local; I need to add them to our spreadsheet but every member has their own tab on the worksheet I was sent. Is there a way to consolidate the tabs without having to copy & paste? The data is in the same position on each tab. Thank you!

2014-12-04 15:34:48


Thanks Charlie - I had considered that but was hoping to accomplish it using the Macro. This thing works pretty slick and I am sure some Macro guru's out there know what the fix is or maybe it just cant be done - I noticed another user had the same issue (Asger) back in Jan 2013

2014-12-04 14:44:01


Ok Steve - just in case nobody comes up with a more elegant solution, here is an idea I came up with that accomplished what I wanted: Before running the macro, shift-select all of the sheets in the workbook, & then select the column(s) containing the formula & copy & paste special/values in place. By first selecting all of the sheets, performing this action on one sheet produced the same result on every sheet. Then you can run the above macro and you will get only data, & no formulas.

If you want to preserve the original formulas on the original sheets, you can copy the new "Combined" tab to a new workbook and save under a different name.

2014-12-04 14:29:34


That's funny - I found this same macro on the same day as Steve, and I have the same question - how can you combine just values from each worksheet, not formulas? My first thought was to edit the macro and replace Paste with Paste Special Values, but on closer inspection I saw there is no paste command in the macro! Must be magic!

2014-12-04 11:03:33


This is fantastic - I don't work in VBA, so being able to cut and paste this and run it is FANTASTIC - Is there a way to copy only the values over to the combined sheet created? - Some of my sheets have formulas that allow for the user to enter a quantity and then that quantity multiplies down through that sheet - The multipliers can be different on the separate sheets. Thanks in Advance

2014-12-01 07:07:09

Ravi Sailesh

It saved a lot of time and effort.
Combined 62 pages at one go.

Thanks a lot

2014-11-24 11:51:25


How would I be able to modify to be able to block a 4 sheets (exactly the same) from different workbooks and combine into one worksheet?

2014-10-29 16:38:03


Thank you very useful macro :)

2014-10-16 01:37:18


Thank you for the code ... it has saved me from alot of manual work...
thanks again.

2014-09-04 06:24:43


Very useful... Thanks

2014-06-20 05:15:46


Hi again!
I wanted to know how I can modify this macro so that when I make changes to the combined sheet, those changes are also made in the original sheets and viceversa.

2014-05-22 12:05:16


Your macro is excellent, easier to use than RDB Merge, and extremely useful for my thesis.
Student from University of Cambridge UK

2014-04-03 13:56:05


Thank you...This helped save me hours..

2014-02-25 03:51:42


if we have different
sheet1 start in A1
sheet2 start in A2
HOW we can solve it ?

2013-12-03 04:09:47

Simon Murray

If only someone could re-post that macro explaining which parts to change for what!
I have the same data in a same sized table in one partricular named sheet in many workbooks that i want to combine...HELP PLEASE.

2013-10-31 17:33:32

Greg Mouning

FYI, when I tried to implement the changes provided by "Michael (micky) Avidan 04 Aug 2013, 06:52" this macro failed. First, I had to define SH as a Variant to the top of my macro as follows:

Dim SH as Variant

Then the macro work successfully!

2013-10-30 09:15:40


Thank you so much!

2013-08-04 06:56:28

Michael (micky) Avidan

By the way - the command:
Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
Allows to use the code in ALL(!) Excel versions (2002-2013) because it ia not limited to row 65536.
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-08-04 06:52:37

Michael (micky) Avidan

Try to replace the Looping section with the following commands:
On Error Resume Next
For Each SH In ActiveWorkbook.Sheets
If SH.Name <> "Combined" Then
Application.GoTo Sheets(SH.Name).[a1]
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
End If
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-08-03 13:03:23


The macro assumes the subsequent sheets are named as Sheet1, Sheet2, and so on. Therefore, it works fine.

I've a workbook with each differently named sheets. Can we tweak the macro to ignore the sheet names, and rather look at all the sheets one after another? Pls advise

2013-08-01 08:09:53


Excel Combine = Urgh
Macro = Brilliant

Thank you.

2013-07-31 22:04:23


Update2: I tried substituting in Arhant's code even though the problem he described was not what I was experiencing, and it FIXED THE PROBLEM! Thanks so much Arhant AND Allen for a great algorithm.

2013-07-31 17:05:13


UPDATE: Even on the first run of the macro ... if row 2+ is blank ... it pulls in the title bar from that page and populates the consolidation page with the title bar data.

2013-07-31 17:02:35


Awesome Macro ... HOWEVER, I have a problem and haven't figured it out quite yet ... Any suggestions???

If I run the macro it pulls in and consolidates all of the data into the new sheet and everything looks great ... but ... If I reun the macro a second time (as someone might do accidently) ... then it pulls in the heading (title bar) of any "blank" spreadsheet ... that is ... any spreadsheet that has a title line on Row 1 ... but ... has no content in rows 2 on .... will populate the consolidation sheet with the title row.

Very strange ... can't figure out why it would do that ...

Any Help?

2013-07-08 13:29:01


Thank you very much for the macro and for Arhant's tips re Selection.CurrentRegion.Select
as my worksheets had titles separated by a blank line. Worked a treat!

2013-06-30 02:18:42


Great Man! Thanks so much :D

2013-05-30 00:05:49

Mike D

This ROCKS! Thank you!!

2013-05-02 05:55:36


Dear Sir,
1) i have 1000 File names with Path (All Files are Different Subfolders)
2) i need $H$11 value in every file.

Please help on this.

2013-04-29 13:12:45

Suraj Singh

Hi Sir,

That is so cool script.

2013-04-26 09:16:43

Mark Curtin

Works great. Thank you.
Is there any way to have the macro only run or apply to certain worksheets? In my case I would like it to only run on Worksheets that have the word "Tasks" in the worksheet name.

2013-04-09 14:31:05


Hi, thanks so much for your script, it's working almost perfectly. Could I ask you how to do 2 extra things in your script?

I'd like it to paste the cells on the new worksheet starting at column A4 instead of the first available line?

Also, is there a way to automatically resize the cells to fit the text?

Again, thank you for this script, it's excellent!

2013-03-15 17:34:56


The macro worked perfect! I combined 398 spreadsheets within the same workbook into one combined spreadsheet. Wonderful!

2013-03-04 09:37:55


This macro has a couple of limitations which can affect the consolidation:-

1. It assumes that the headings are on Row 1 (this is true in most cases else some of the references to Source sheet A1 can be changed as required)

2. "Selection.CurrentRegion.Select ' select all cells in this sheets" - This actually selects only the current region of contiguous data. ie if there are a lot of blanks between data, a lot of data might be missed out. The way out of this would be to use this code:

Range(Selection, Selection.SpecialCells(xlLastCell)).Select

3. This code "Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)" uses column A to search for the last updated data row, which causes a problem. Eg. All the data from a sheet is copied onto the consolidated sheet, but this data has column A blank, then the code will end up overwriting all the previously pasted data.

The solution is to ensure that the last cell in column A of every sheet is filled with any data (ie. not blank). Then this will work fine. Alternatively a macro to fill blanks in all the sheets with spaces may also work fine

2013-02-15 07:35:55


Hi, I have tried this macro (i have a workbook with 2400 sheets)

each sheet has a different number of lines but no more than 27 and each has identical columns upto J. when i run the macro on the combined page all it shows is one line of data which is the first line where there are some merged cells on each worksheet.

What am i doing wrong?

2013-02-13 06:59:41


Macro worked prefectly for me. thanks

2013-02-07 06:10:32


how to excel sheets data's compile in one sheets

2013-01-30 03:19:08


Dear All
please help me how marge multipale workbook data in one workbook.

2013-01-22 15:18:06


This seemed to work well, but for some reason it selects some of the rows correctly but on other sheets in only selects the first two column. Its as if these were the previously highlghted data. Is there a way to tell it to be sure the selection is correct?

2013-01-22 10:12:33


This macro is amazing. Thanks a lot.
I do have one problem though. How do i make the macro copy values instead of funktions?

2013-01-21 08:00:48



2013-01-16 06:38:47


Thank you so much!!!
The easiest code I've found in internet!

2013-01-11 11:58:34


I want to take rows from many identical sheets and compile them. So if 5 sheets have 5 rows, the summary sheet should have 25 rows. The consolidate is adding the information in the cells so the summary sheet has only 5 rows. I don't want to add anything only compile my rows. Any advice on doing this without a macro? Thanks.

2013-01-11 03:50:24


that is ok but i have to move a row of column G if its any of the field contains "india" as a value. so for that what to do....

2013-01-04 21:56:58


I run it but the data I am combining has a ' as the first character in any cell that actually has data. Is there a way to amend the macro to remove the ' and bring the rest of the data?

2013-01-04 15:29:06


God Bless You!
I looked for two days and then cam across this. I tweeked it and it works great.
Can't thank you enough.

2012-12-07 11:05:57


This macro worked! Thank you sooooo much!!!!

2012-11-16 11:25:17


The macro worked perfect! This saved my team so much time. I was the office hero for about 15 minutes...

I can't THANK YOU enough.

2012-11-15 14:59:57


thank you, this macro worked perfectly for me, no tweaking or anything. Really great- you saved me a ton of time! I can't get "consolidate" to work the same way, but I don't care!

2012-09-19 13:05:48


Thank you very, very much!

Would have taken me much time to figure out since I'm not usually programming in VB and I have no knowlege about Excel's object model.

2012-09-18 18:50:36

Mark Bryson

Totally rocked, thanks!

2012-07-30 05:01:47

debanjan chanda

Will it work if the sheet name is not Sheet 1,2 or 3 but some thing esle.

2012-07-17 00:48:00


Great! Thank you for sharing. I helped me finished my task and spend more time with my family. I appreciate you posting this online. Cheers!

2012-06-18 04:55:36


I have consolidated multiple worksheets into one. The problem i have is that i have added another line on the source data and that line is not showing on the consolidated worksheet even though it is within the consolidated range. Please help.

2012-06-06 14:47:27

Gregory Mouning

Hi Allen,

I tried to use the Combine Macro on my 2007 Excel spreadsheet and the data from my Worksheets are not combined into one Excel worksheet. The macro appears to transverse through each of my worksheets but creates an empty worksheet.

Can you tell me what I am doing wrong?

2012-05-04 16:01:11


Hi Allen,

This works great! However, would it be possible to add a new column to the Combined worksheet with value = <worksheet name>?

So, on the combined worksheet, a new column is added at the end which will contain the name of the worksheet from which data is copied.


2012-04-28 18:30:42


it woudl be useful if you woudl include full detailed examples and screen shots

2012-03-22 09:54:36



Thanks this works nicely.

Do you know if there is a way to also copy the row/column formatting?

For example the column width.

Thanks again.

2012-02-29 10:04:09


Sorry - I meant the Consolidate tool from Excel.

Your macro is working beautifully. Is there any way to get it to just consolidate selected worksheets? I have a workbook with a growing number of sheets, but just want to consolidate 4 at a time.

2012-02-15 13:58:54


What is the "this" that didn't work for you? There are multiple techniques described in the tip.

2012-02-15 13:55:54


This didn't work for me; it only consolidated the first row - so I had a "combined" sheet of three duplicate rows and that was all.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.