Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Condensing Multiple Worksheets Into One

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.

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
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    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("A1").Select
        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)
    Next
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.

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.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Comments for this tip:

Julie    12 Dec 2014, 12:58
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!
Steve    04 Dec 2014, 15:34
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
Charlie    04 Dec 2014, 14:44
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.
Charlie    04 Dec 2014, 14:29
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!
Steve    04 Dec 2014, 11:03
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
Ravi Sailesh    01 Dec 2014, 07:07
Excellent.
It saved a lot of time and effort.
Combined 62 pages at one go.

Thanks a lot
Luke    24 Nov 2014, 11:51
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?
Thanks.
Luke
Vic    29 Oct 2014, 16:38
Thank you very useful macro :)
Raghava    16 Oct 2014, 01:37
Thank you for the code ... it has saved me from alot of manual work...
thanks again.
Vishal    04 Sep 2014, 06:24
Very useful... Thanks
Student    20 Jun 2014, 05:15
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.
Thanks!
Student    22 May 2014, 12:05
Your macro is excellent, easier to use than RDB Merge, and extremely useful for my thesis.
Student from University of Cambridge UK
Danny    03 Apr 2014, 13:56
Thank you...This helped save me hours..
rasha    25 Feb 2014, 03:51
if we have different
Range
example:
sheet1 start in A1
sheet2 start in A2
HOW we can solve it ?
Simon Murray    03 Dec 2013, 04:09
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.
Greg Mouning    31 Oct 2013, 17:33
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!
Tatiana    30 Oct 2013, 09:15
Thank you so much!
Michael (micky) Avidan    04 Aug 2013, 06:56
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)
ISRAEL

Michael (micky) Avidan    04 Aug 2013, 06:52
@Kavin,
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.CurrentRegion.Select
           Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
           Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
      End If
Next
-------
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Kavin    03 Aug 2013, 13:03
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
Jamie    01 Aug 2013, 08:09
Excel Combine = Urgh
Macro = Brilliant

Thank you.
Greg    31 Jul 2013, 22:04
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.
Greg    31 Jul 2013, 17:05
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.
Greg    31 Jul 2013, 17:02
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?
Maggie    08 Jul 2013, 13:29
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!
Denan    30 Jun 2013, 02:18
Great Man! Thanks so much :D
Mike D    30 May 2013, 00:05
This ROCKS! Thank you!!
raj    02 May 2013, 05:55
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.
Suraj Singh    29 Apr 2013, 13:12
Hi Sir,

That is so cool script.
Mark Curtin    26 Apr 2013, 09:16
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.
Troy    09 Apr 2013, 14:31
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!
Lisa    15 Mar 2013, 17:34
The macro worked perfect! I combined 398 spreadsheets within the same workbook into one combined spreadsheet. Wonderful!
Arhant    04 Mar 2013, 09:37
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





lucy    15 Feb 2013, 07:35
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?
Keith    13 Feb 2013, 06:59
Macro worked prefectly for me. thanks
kaviraja    07 Feb 2013, 06:10
how to excel sheets data's compile in one sheets
kavita    30 Jan 2013, 03:19
Dear All
please help me how marge multipale workbook data in one workbook.
Nfolgers    22 Jan 2013, 15:18
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?
Asger    22 Jan 2013, 10:12
This macro is amazing. Thanks a lot.
I do have one problem though. How do i make the macro copy values instead of funktions?
Manoj    21 Jan 2013, 08:00
Great..........
Lucas    16 Jan 2013, 06:38
Thank you so much!!!
The easiest code I've found in internet!
Jacob    11 Jan 2013, 11:58
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.
shiva    11 Jan 2013, 03:50
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....
job    04 Jan 2013, 21:56
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?
J    04 Jan 2013, 15:29
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.
Lynette    07 Dec 2012, 11:05
This macro worked! Thank you sooooo much!!!!
Monique    16 Nov 2012, 11:25
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.
lal    15 Nov 2012, 14:59
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!
Nico    19 Sep 2012, 13:05
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.
Mark Bryson    18 Sep 2012, 18:50
Totally rocked, thanks!
debanjan chanda    30 Jul 2012, 05:01
Will it work if the sheet name is not Sheet 1,2 or 3 but some thing esle.
Kerwin    17 Jul 2012, 00:48
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!
Ronald    18 Jun 2012, 04:55
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.
Gregory Mouning    06 Jun 2012, 14:47
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?
Amit    04 May 2012, 16:01
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.

Thanks.
Amit
AL    28 Apr 2012, 18:30
it woudl be useful if you woudl include full detailed examples and screen shots

vince    22 Mar 2012, 09:54
Hi,

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.
Christine    29 Feb 2012, 10:04
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.
awyatt    15 Feb 2012, 13:58
What is the "this" that didn't work for you? There are multiple techniques described in the tip.
christine    15 Feb 2012, 13:55
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.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.