Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Condensing Multiple Worksheets Into One.

Condensing Multiple Worksheets Into One

by Allen Wyatt
(last updated March 28, 2020)

10

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. Excel does not include a tool that allows you to do this automatically, but it is a great candidate for a macro. Remember, though, that the structure of each worksheet you are condensing should be identical.

The following macro steps through all the worksheets and combines the data to a new worksheet that it adds at the beginning of the workbook.

Sub Combine()
    Dim J As Integer
    Dim s As Worksheet

    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")

    For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.GoTo Sheets(s.Name).[a1]
            Selection.CurrentRegion.Select
            ' Don't copy the headings
            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
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.

Note:

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 (8884) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel 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. ...

MORE FROM ALLEN

Printing All Open Documents

Have a bunch of documents you need to print? If all the documents are open, you can use a handy little macro to print ...

Discover More

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Maintaining Formatting when Inserting Documents

Word allows you to easily insert the contents of one document into another. Doing so, however, may result in unintended ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Quickly Copying Worksheets

Excel provides a little-known way to copy worksheets simply by clicking and dragging. Here's how to do it.

Discover More

Quickly Inserting a New Worksheet

Want a quick way to insert a worksheet? There's nothing faster than using the handy shortcut.

Discover More

Retrieving Worksheet Names

Want to grab the names of all the worksheets in a workbook? Here's how you can stuff all those names into the cells of a ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 two minus 0?

2020-06-29 10:05:47

Kathrin

Thank you very mich for that makro! Very helpful! Could you maybe help me with one question? My heading contains 5 instead of 1 row. How could I adjust that?

Thank you very much in advance!

Best


2020-04-23 05:12:34

Alan Elston

Hi Andrea
It is a bit different what you want.
It is a very common thing to do, what you want. But giving you a full macro here will look a bit messy in the confines and format limitations of a Comment section. It is a complete new macro you need , not just a few changes to Allen Wyatt's above.
A Google search with like “VBA combining multiple workbooks into one” will flood you with examples.

If you then need further help it would probably be best to ask at a help forum, such as excelforum com or mrexcel com
You will get a quick answer there. If you are not in a rush I would do it for you over at excelfox com when I have time

Alan Elston


2020-04-22 19:49:13

Andrea

Hello,
This is exactly what i need except that the source is from different workbooks/files called FC_1, FC_2,FC_3... stored in the same directory.
The sheets is called "Account" for all files and have the same format. please can you help me?
Thanks
Andrea


2020-04-21 05:35:03

Alan Elston

Edit should be
Dim Cnt As Long


2020-04-21 04:49:52

Alan Elston

Hello Tina Glynn
The macro from Allen Wyatt is looping for each worksheet by virtual of these two lines
For Each s In ActiveWorkbook.Sheets
Next

s is Dimensioned ( declared ) as a worksheet

You can do it a bit differently in your case:
_ You can ignore the s
_ In VBA you can reference a worksheet by its Item number, which is its Tab number counting from the left.
_ Allen Wyatt’s macro adds a new worksheet at tab number 1, so then your first 3 tabs will shift to the right by one place and will then have the item numbers of 2 3 and 4

An alternative Looping section for you would be something like:

Dim Cnt
For Cnt = 2 to 4
Application.GoTo Worksheets.Item(Cnt).[a1] ' This will go to the second, third and Forth worksheet, Items 2, 3 and 4
Selection.CurrentRegion.Select
' Don't copy the headings
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)
Next Cnt



Alan Elston


2020-04-20 15:16:15

Tina Glynn

Can this be used to only combine 3 worksheets and then stop? I have a workbook with 15 sheets, but I only want this to work for the first 3 sheets.


2020-04-20 12:54:31

Peter

I understood, thank you, gentlemen, for your time!


2020-04-18 05:04:38

Alan Elston

Hi Peter…….The full syntax of what Allen Wyatt is using is like …….Cells(Rows.Count, 1).End(xlUp).Item(2) ……..….Item(2) will give us the cell just below the cell given by…….Cells(Rows.Count, 1).End(xlUp) ………….Cells(Rows.Count, 1).End(xlUp) is the same as …….Cells(Rows.Count, 1).End(xlUp).Item(1) ……………….It is not to easy to explain how the items are assigned for a range……See the demo that I have done for you here:…….…. ----- http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13070&viewfull=1#post13070 ----- ….As you will see, item numbers are not restricted to the range given. They keep going. …Cells(Rows.Count, 1).End(xlUp) returns just one cell…..But item(2) is the cell beneath…. Item(3) is two cells beneath ….etc…. ……As you will see, Item numbers are not restricted to just the range itself. The item numbers keep going. They go in a sequence of ... all columns in a row, ... then the next row ... etc........... The column count is determined by the original range, but the rows are not limited.…………Alan Elston


2020-04-18 04:49:12

Peter Atherton

Peter
If Cells(Rows.Count, 1).End(xlUp).Row gives the last row number then Cells(Rows.Count, 1).End(xlUp)(2) gives an offset of 1 row. You can test this in some data with the line Cells(Rows.Count, 6).End(xlUp)(2).Select


2020-04-17 16:24:14

Peter

Thank you, I come here ofter for the useful information. Can you please tell what digit "(2)" means in the end of

Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)

I can't figure out..

Thanks,


This Site

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

Newest Tips
Subscribe

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.