Written by Allen Wyatt (last updated March 28, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8884) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Condensing Multiple Worksheets Into One.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Have you ever deleted a worksheet by mistake? Once you get over the sick feeling in your stomach, you will start casting ...
Discover MoreIf you want someone to not be able to move from one worksheet to another in a workbook, you've got your work cut out for ...
Discover MoreExcel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-02-18 13:42:10
Hello Amita
You can refer to a worksheet by its tab number , ( also known as its item number or Index).
In Allen Wyatt’s macro, you can see that he is looping all worksheets via this code section
For Each s In ActiveWorkbook.Sheets
'
'
'
'
'
'
Next s
That code section is responsible for looping through all worksheets.
If you were to modify that code section something like the following, then you would only be looping from the 7th tab
For Each s In ActiveWorkbook.Sheets
If s.Index > 6 Then
'
'
'
'
'
'
Else
End If
Next s
Alan Elston
2021-02-17 18:56:20
Amita
Can this be used to only combine sheets after a certain number? For example I have a workbook, where the number of sheets after sheet 6, can vary in number which are the sheets I want to combine. I essentially want to use a greater than sheet 6 function....
2020-11-27 13:18:22
Peyton Brown
This macro worked really well to combine the worksheets. I was trying to copy the data in the combined worksheet into another workbook but I keep getting the message "The information cannot be pasted because the Copy area and the Paste area are not the same size and shape." I have been trying to paste cell values and only have my cursor in a single cell. Any advice?
Thank you very much for the help!
2020-06-29 10:05:47
Kathrin
Thank you very mich for that makro! Very helpful! Could you maybe help me with one question? My heading contains 5 instead of 1 row. How could I adjust that?
Thank you very much in advance!
Best
2020-04-23 05:12:34
Hi Andrea
It is a bit different what you want.
It is a very common thing to do, what you want. But giving you a full macro here will look a bit messy in the confines and format limitations of a Comment section. It is a complete new macro you need , not just a few changes to Allen Wyatt's above.
A Google search with like “VBA combining multiple workbooks into one” will flood you with examples.
If you then need further help it would probably be best to ask at a help forum, such as excelforum com or mrexcel com
You will get a quick answer there. If you are not in a rush I would do it for you over at excelfox com when I have time
Alan Elston
2020-04-22 19:49:13
Andrea
Hello,
This is exactly what i need except that the source is from different workbooks/files called FC_1, FC_2,FC_3... stored in the same directory.
The sheets is called "Account" for all files and have the same format. please can you help me?
Thanks
Andrea
2020-04-21 05:35:03
Alan Elston
Edit should be
Dim Cnt As Long
2020-04-21 04:49:52
Hello Tina Glynn
The macro from Allen Wyatt is looping for each worksheet by virtual of these two lines
For Each s In ActiveWorkbook.Sheets
Next
s is Dimensioned ( declared ) as a worksheet
You can do it a bit differently in your case:
_ You can ignore the s
_ In VBA you can reference a worksheet by its Item number, which is its Tab number counting from the left.
_ Allen Wyatt’s macro adds a new worksheet at tab number 1, so then your first 3 tabs will shift to the right by one place and will then have the item numbers of 2 3 and 4
An alternative Looping section for you would be something like:
Dim Cnt
For Cnt = 2 to 4
Application.GoTo Worksheets.Item(Cnt).[a1] ' This will go to the second, third and Forth worksheet, Items 2, 3 and 4
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)
Next Cnt
Alan Elston
2020-04-20 15:16:15
Tina Glynn
Can this be used to only combine 3 worksheets and then stop? I have a workbook with 15 sheets, but I only want this to work for the first 3 sheets.
2020-04-20 12:54:31
Peter
I understood, thank you, gentlemen, for your time!
2020-04-18 05:04:38
Hi Peter…….The full syntax of what Allen Wyatt is using is like …….Cells(Rows.Count, 1).End(xlUp).Item(2) ……..….Item(2) will give us the cell just below the cell given by…….Cells(Rows.Count, 1).End(xlUp) ………….Cells(Rows.Count, 1).End(xlUp) is the same as …….Cells(Rows.Count, 1).End(xlUp).Item(1) ……………….It is not to easy to explain how the items are assigned for a range……See the demo that I have done for you here:…….…. ----- http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13070&viewfull=1#post13070 ----- ….As you will see, item numbers are not restricted to the range given. They keep going. …Cells(Rows.Count, 1).End(xlUp) returns just one cell…..But item(2) is the cell beneath…. Item(3) is two cells beneath ….etc…. ……As you will see, Item numbers are not restricted to just the range itself. The item numbers keep going. They go in a sequence of ... all columns in a row, ... then the next row ... etc........... The column count is determined by the original range, but the rows are not limited.…………Alan Elston
2020-04-18 04:49:12
Peter Atherton
Peter
If Cells(Rows.Count, 1).End(xlUp).Row gives the last row number then Cells(Rows.Count, 1).End(xlUp)(2) gives an offset of 1 row. You can test this in some data with the line Cells(Rows.Count, 6).End(xlUp)(2).Select
2020-04-17 16:24:14
Peter
Thank you, I come here ofter for the useful information. Can you please tell what digit "(2)" means in the end of
Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)
I can't figure out..
Thanks,
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments