

Say you have many worksheets, all in the same structure. You want to combine them into one worksheet. For example copying them underneath each other so that you can conduct lookups or insert PivotTables. In this article, you learn four methods to merge sheets in Excel.
Contents
In many cases it’s probably the fastest way to just copy and paste each sheet separately. That depends of course on the number of worksheets you want to combine and their structure. Some comments:
You can use Excel formulas to combine data from all worksheets. The main formula is INDIRECT.
This method has some disadvantages, though.
On the other hand, it has one major advantage: If you spend effort to set it up, this method is dynamic. That means when your input updates, the merged worksheet updates as well.
Set up the INDIRECT formula for merging sheets.
The INDIRECT formula can access any cell from a link (or better: an address) you provide. Please refer to this article to learn more about the INDIRECT formula. So you only have to provide the addresses for each cell in each worksheet you want to combine. Therefore, you should prepare a worksheet the following way (please refer to the screenshot on the right-hand side):
So let’s assume that you want to get the value from cell A1 of Sheet1. You would need then all the parts ‘Sheet1’, column ‘A’ and row ‘1’. Combining them in the INDIRECT formula would lead to the following formula. The formula in cell C4 is =INDIRECT(“‘”&$A4&”‘!”&C$2&$B4) .
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Copy this worksheet into your Excel file and all sheets are merged automatically.
You want to save some time? We prepared a worksheet which can merge sheets automatically. What do you have to do? Download this workbook (~7 MB) and copy the only sheet into your own workbook. That’s it.
Please note the following comments.
You feel confident enough to use a simple VBA macro? Please insert the following code into a new VBA module. If you need assistance with VBA, please refer to this article.
Sub Merge_Sheets() 'Insert a new worksheet Sheets.Add 'Rename the new worksheet ActiveSheet.Name = "ProfEx_Merged_Sheet" 'Loop through worksheets and copy the to your new worksheet For Each ws In Worksheets ws.Activate 'Don't copy the merged sheet again If ws.Name <> "ProfEx_Merged_Sheet" Then ws.UsedRange.Select Selection.Copy Sheets("ProfEx_Merged_Sheet").Activate 'Select the last filled cell ActiveSheet.Range("A1048576").Select Selection.End(xlUp).Select 'For the first worksheet you don't need to go down one cell If ActiveCell.Address <> "$A$1" Then ActiveCell.Offset(1, 0).Select End If 'Instead of just paste, you can also paste as link, as values etc. ActiveSheet.Paste End If Next End Sub
Combine worksheets with Professor Excel Tools
You like to use the most convenient way? Try the Excel add-in Professor Excel Tools.
Alternatively, you can further refine your desired settings: Do you want to add the original sheet name in column A? No problem.
Also, define the copy & paste mode as shown in the screenshot on the right-hand side.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 3
DIEGO
Thank for article!
Method3 contains the first working macro I found in order to merge sheets!
(the other 4-5 macro I used ever cut one row at begin or end of each sheet to merge)
David
The downloadable workbook saved me a lot of time, but I found an error. I noticed that the 5th tab of my workbook wasn’t being brought into the data table with the other tabs. This is due to a formula error in cell A12. You will notice that the ROW() references jump from A4 in cell A11 to A6 in cell A12. This causes the 5th tab of the workbook to be excluded.
Carlos
On method 4, I am getting the follow error:
At line ‘Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)
I am receiving the follow error:
Run-time error 1004
Method Open of object Workbooks fail
Any feedback please