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


Grabbing the Second-to-Last Value in a Column

Need to get at the next-to-last value in a column, regardless of how many cells are used within that column? This tip ...

Discover More

Maintaining Accuracy of Significant Digits

If you work in the sciences or mathematics, you know that significant digits are important. This tip answers questions ...

Discover More

Incorrect Suggested Correction

As you type your document, Word flags words and phrases it thinks may be incorrect. The source of these various flaggings ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Finding the Size of Individual Worksheets

Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas ...

Discover More

Determining a Worksheet's Number

When you add a new worksheet to a workbook, it receives a meaningful name such as "Sheet4" or "Sheet17." If you want to ...

Discover More

Copying a Worksheet

Need to make a copy of one of your worksheets? Excel provides a few different ways you can accomplish the task.

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 2 + 8?

2019-09-20 13:28:32


Is it possible to enter a reference to the sheet rows are copied from in a separate column? For example, I have sheets 1 through 72 I am using this to combine into one sheet for review. Would it be possible for every row that is copied to be labelled as from Sheet 1, Sheet 2, etc.


2019-09-20 13:18:17


Is it possible to enter a reference to the sheet rows are copied from in a separate column? For example, I have sheets 1 through 72 I am using this to combine into one sheet for review. Would it be possible for every row that is copied to be labelled as from Sheet 1, Sheet 2, etc.


2019-09-20 13:14:36


Is it possible to enter a reference to the sheet rows are copied from in a separate column? For example, I have sheets 1 through 72 I am using this to combine into one sheet for review. Would it be possible for every row that is copied to be labelled as from Sheet 1, Sheet 2, etc.


2019-07-30 11:55:19


Hi There,

Ex : I have 40 Employees His 40 Names and i had copied 40 Sheets in one workbook. In that copied sheet there have only one employee name and we have 40 names data in primary sheet and we have to copy and paste the data in multiple times based on primary sheet names data can you please any one help us on that.....

2019-04-18 00:10:03

Swapnil Gaikwad

Hello All,
I need macro to fetch/copy data from multiple excel files(different sheets in those files) into one excel sheet.
Can anyone please help me

2019-03-19 10:10:40


Hi, I have a slightly different problem statement. I have an excel file with multiple worksheets, having 2 rows but with different headers. Also header count is not constant across all sheets. I need to merge the sheets in a way that the row counts do not increase, instead the data will keep on added column-wise.

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.