Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 April 8, 2017)


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

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

    For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.GoTo Sheets(s.Name).[a1]
            ' 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
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 (8884) applies to Microsoft Excel 2007, 2010, and 2013. 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. ...


Multiple Envelopes in One Document

Want to save a bunch of envelopes in a single document so that you can print them all out as a group? Here's how to ...

Discover More

Coloring Identical Company Names

Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the ...

Discover More

Widening Multiple Columns Proportionally

It is easy to adjust the width of columns in Excel. It is much harder to adjust the width of a range of columns ...

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)

Lotus Grouped Worksheets

Not all spreadsheet programs are created equal; there are some things that can be done in others that can't be done in ...

Discover More

Freezing Top Rows and Bottom Rows

Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so ...

Discover More

Determining the Complexity of a Worksheet

If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how ...

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 3 - 2?

2017-09-27 11:04:41



2017-05-23 12:11:30



I've been using this solution for a while and at times it works and fully combines my sheets, but other times, it'll only combine the first 65,070 records and then the code stops running. Is there a particular reason for this? I made sure all other apps were closed and that my pc wasn't consuming too much RAM when running the process.

2017-05-11 10:30:22

William Najar

Awesome job. Thank you for sharing.

2016-10-19 18:17:20

Javed bin Ali

Hi Allen,

This has been a great help. I have a different set of requirements like

a) i have 10 sheets which are protected with a username and password thru userform. only authorised staff can see their particular sheet where they would be able to enter data and those data would go to the combined or master sheet.
b) i need to clear the data from specific sheet as soon as they are updated in the master sheet.

Please help me to resolve this. Thanks.

2016-09-16 15:24:42


Can a sheet be omitted??

2016-08-29 12:09:16


I want the data, when entered on the subordinate sheets, to update the Master sheet. Could some one help. In short how to update the combined sheet. Thanks!

2016-08-25 13:01:27


How to update the combined sheet if any changes are made? This code worked, was very helpful. Thanks!

2016-07-05 18:50:02

Justin Wong


My last worksheet was also being duplicated. I couldn't figure out why so I added a print statement:
"Debug.Print "Sheet name: "; s.Name"
to see which sheets were in the workbook. Turns out that one of my macros was being counted as a sheet.

I added the and condition to the original if statement like this:
If s.Name <> "Combined" And s.Name <> "Macro1" Then

Not the cleanest solution, but it worked.

2016-04-14 01:48:47


Thanks a lot..I improvised upon this code and my work was done.

2016-04-07 17:18:12


You have just saved me so much time!
Thank you so much

2016-03-30 10:47:10

Stevie B

I have a spreadsheet with one Master sheet and 4 subordinate sheets. I want the data, when entered on the subordinate sheets, to update the Master sheet. I tried the macro list above and it didn't work. My sheets are in this order: Master Requirements, AM - Asset Mgmt, IM - Inventory Mgmt, PM - Purchasing Mgmt, and WM - Work Mgmt. Can someone tell me what I am doing wrong, please?

2016-03-16 07:13:11


Need all data from different work sheets to one worksheet ex: Jan,feb,march having data with list of names and working hrs . I need in a table format like employeename Jan Feb March
1 5 6 8
2 8 7 6

can you please send me the code for above example.



2015-08-25 11:28:31


Yay! Thank you so much!

2015-07-24 08:08:09


This code is very helpful. What if I only need to combine the first 3 sheets in the workbook?


2015-07-01 12:57:58


Is there a way to alter the position where the data will start pasting to a different column as well instead of starting in column A?

2015-06-24 17:20:02


This is awesome!

I tried this macro in a test file with only 2 worksheets, however the last worksheet seems to be duplicated in the combined file.
I did change the code slightly (after trial and error) to allow for my two rows of headers (1's changed to 2's in line of code noted below):

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

Thanks so much,

2015-06-10 19:29:32

Peter Atherton


I think that Access database is a better choice for combining pics and data.


2015-06-09 10:10:51


I have 70 tabs that I am trying to combine and this VBA code works great except that it stops at about the 50th tab. Is there a max or a limit to the number of tabs?

2015-05-21 15:11:32


This process is awesome. Thank you.

I'm having one small problem. When I run the macro, I end up with the headings copied from every tab. I only need them once. I tried the ' do not copy headings but it didn't make a difference.


2015-04-05 15:17:34


I created a document for estate & insurance purposes by inserting photos and typing captions into cells below them. The photos are spread across several worksheets by category but the finished file is awkward...would be much better in one combined worksheet. I'd like to accomplish that without copying and pasting every photo and caption.

Is this application of Excel appropriate for what I want to do? I've never worked with macros. Don't know where to begin but willing to learn.

Excel is a favorite program. Glad to find this website.

2015-03-26 12:11:12


Thanks a lot, you saved me a lot of work!

2015-03-17 10:08:02


Hi, I tried it and macro creates a new sheet, called Sheet1 but nothing happens. I do not get all data from the various sheets copied/pasted into one sheet. Anyone can help?
Thanks in advance!

2015-02-26 16:07:34


awesome ! thanks !

2015-02-21 06:07:14


Man this is great! Is there a way I can skip a tab and not include it in the combine? For instance I have a summary tab as the first tab in my workbook that summarizes different information from another workbook. It pulls all the data from the other sheets exactly as I want it but it also pulls all the data from the first tab...

2015-01-14 12:35:22


Sweet, thank you very much!!

2015-01-07 12:59:29



You Rock


Thanks !!!!

2015-01-07 09:11:32


I have 3 sheets, 2 have 64000 rows and 1 about 30000 and this seems to only copy the first sheet, does it have something to do with the number of rows? Sorry, I'm not the best with this stuff, just winging it...

2015-01-06 13:38:25

Dan Leung

One bug I found is that the lowest left most cell of the region you intend to combine cannot be empty. Otherwise, the macro will paste data to the "combine" worksheet from the last non-empty cell in the A column.

2014-12-11 06:09:47


Awesome, came to my rescue at critical moment.

2014-12-09 05:29:13


Thanks. It works for me.

2014-12-05 08:45:08


Novice in VBA. The basic copy and paste works but losing data from worksheet to "combined". Is there something I need to add or missing to adapt this specific to my workbook?

Much appreciated. Just started subscribing. Good stuff here.

2014-10-31 02:58:46


My friend you did a great job really.
it saved my time a lot.
i will simply say
welldon bro

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

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.