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)

31

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.

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

MORE FROM ALLEN

How to Paste Non-Floating Pictures with a Macro

Using a macro to paste pictures inline instead of floating in Word 97.

Discover More

Canceling a Command

Need to cancel a command you've already started? It is as easy as pressing a single keystroke.

Discover More

Error when Double-Clicking Workbook Files

When you double-click an Excel workbook on your system, Windows has to do a lot of behind-the-scenes work to start Excel and ...

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)

Testing for an Empty Worksheet

If you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. ...

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

Discover More

Unbreakable Formula References to Worksheets

Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name of ...

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. 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 6 - 0?

2017-05-23 12:11:30

Maria

Hello,

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

Brett

Can a sheet be omitted??


2016-08-29 12:09:16

Sun

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

mary

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

@Terri

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

Deepjyoti

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


2016-04-07 17:18:12

JaLoCo

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

sreekanth

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.

Regards,

Sreekanth


2015-08-25 11:28:31

Steff

Yay! Thank you so much!


2015-07-24 08:08:09

anne

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

Thanks


2015-07-01 12:57:58

Whitney

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

Terri

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


2015-06-10 19:29:32

Peter Atherton

@DianeM

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

HTH


2015-06-09 10:10:51

Tom

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

Mary

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.

Help?


2015-04-05 15:17:34

DianeM

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

Quattie

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


2015-03-17 10:08:02

Piquiita

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

skr

awesome ! thanks !


2015-02-21 06:07:14

Jon

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

CDG

Sweet, thank you very much!!


2015-01-07 12:59:29

Rob

Hi

You Rock

Brilliant

Thanks !!!!


2015-01-07 09:11:32

Josh

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

Phil

Awesome, came to my rescue at critical moment.


2014-12-09 05:29:13

Karthik

Thanks. It works for me.


2014-12-05 08:45:08

JohnB

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

KAshif

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