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.
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 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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how you ...Discover More
Not all spreadsheet programs are created equal; there are some things that can be done in others that can't be done in Excel. ...Discover More
Want the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make them ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.