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 June 23, 2018)


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.


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, 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. ...


Converting Text to Comments

One of the strong suits of macros is that they can process the information in a document quickly and reliably. For ...

Discover More

Using Copy and Paste for Formatting

Want to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special ...

Discover More

Determining a Value of a Cell

Cells can store all sorts of information that can be formatted and displayed in a myriad of ways. If you want to quickly ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Freezing Worksheet Tabs

If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...

Discover More

Identifying the Last Cell Changed in a Worksheet

When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It ...

Discover More

Finding a Worksheet with a Specific Value in a Specific Cell

If you have a lot of worksheets in workbook, finding the exact one you want can be a bit tricky. This tip looks at ...

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 5 + 3?

2018-10-11 04:58:32



I need to apply this code to only specific worksheets inside my workbook.
How can I do this?

I am tryong to automate the creation of some monthly variances analysis report.

Thank you all,

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.