Copy data from multiple worksheets into one
When you work with multiple sheets located in different Excel files, you may need to bring certain records together. Copy Sheets lets you paste data from several spreadsheets to one list or workbook.
Video: How to merge Excel worksheets into one master worksheet
Before you start
Please note that the add-in works with visible rows only. Filtered, grouped, and hidden data will be ignored.
How to use Copy Sheets
Start Copy Sheets
There are two ways to run the tool:
- Click on the Copy Sheets icon on the Ablebits Data tab in the Merge group:
- Or click on the down arrow under the add-in icon and pick the necessary option in the drop-down list to get straight to step 2:
Step 1: Choose the way to merge your worksheets
Decide how to copy your worksheets into one:
- Copy sheets in each workbook to one sheet. Place the resulting worksheets to one workbook.
Select this radio button to transform the selected workbooks into sheets and get the resulting tables in one file:
- Copy data from the selected sheets with the same name to one sheet.
Use this option if your task is to merge several Excel files into one by combining identically named worksheets and placing all resulting sheets to one workbook:
- Copy the selected worksheets to one workbook.
Paste the needed sheets from different Excel files to one book:
- Copy data from the selected worksheets to one sheet.
Use this option to create one table with data from multiple sheets:
Note. If you select the Copy data from the selected sheet to one sheet option, the wizard will have an additional step which offers you to choose where to place the results, to a new workbook or a new worksheet.
When the option is chosen, click Next to continue.
Step 2: Select worksheets and ranges to combine
You will see a full list of files open in Excel on this step. Tick off the checkboxes next to the names of the worksheets you want to copy.
Tip. If you have a long list of files, take advantage of the checkbox next to the Worksheets column. It lets you instantly select and deselect all items in the list.
Besides, you can use the additional options:
- Check Use the same range on all selected sheets to get the same range selected automatically across all the copied sheets.
- If you tick the Select sheets with the same name in all workbooks option and then start selecting sheets for copying, the tool will automatically tick sheets with the same name in other workbooks.
- By default, the add-in pulls all data ranges from the sheets. However, you can adjust that by using the Select range icon next to the sheet name in the list. It will show a dialog box, so you could pick the needed cells:
Also, you can benefit from several additional options at the bottom of the wizard's window:
- Collapse all / Expand all - click this button to quickly hide or show all sheets in each workbook.
- Exclude - remove the selected workbooks from the add-in window.
- Add files... - use this button to open additional Excel books.
Click Next to choose other merging options.
Step 3: Configure additional pasting options
This step lets you specify how you want to copy and paste the records into the resulting sheet:
Choose how to paste the data
- Paste all. If the copied ranges contain formulas, they will be transferred along with the values.
- Paste values only. Select this radio button if you don't want to include formulas in the results.
- Create links to source data. Pick this option if you want the values to be updated automatically when the original worksheets are modified.
Decide on the arrangement of the copied datasets
- Place the copied ranges one under another. Paste records vertically, i.e. data from sheet 2 will appear in the rows below data from sheet 1:
- Place the copied ranges side by side. Paste records from the sheets horizontally, i.e. data from sheet 2 will appear in the columns to the right of data from sheet 1:
Adjust the resulting layout
- Preserve formatting. Tick off this checkbox to save original cell format and the style of the copied ranges.
- Separate the copied ranges by a blank row/column. Get an empty row or column between the data copied from different sheets.
- Select Insert names of the source sheets or workbooks to the first column to see where data in each row come from.
- Copy tables with their headers. Choose this option to paste ranges with their headers or unselect it to exclude the top row from the results:
Responses
Kenneth Seiden says:
December 3, 2024 at 12:23 am
When selecting "Paste All" the formulas are linking to the previous workbook rather than being pasted as they are. For example, I have a SUMIF to another sheet within the workbook and I am trying to use this function to paste additional data onto that sheet when I combine workbooks, but the formulas don't work because it now has the OneDrive info from the previous workbook.
Is there a way to set up paste all where the formulas paste verbatim?
Hello Kenneth,
Thank you for your comment. Sorry, it's hard to understand what might be causing the problem without seeing your data. Please email us to support@ablebits.com with a sample file including the data you have and the result you'd like to receive. Also, you can attach a short video showing the steps you follow. These details will help us faster understand the issue and provide you with a solution. Thank you.
Adesoji says:
May 10, 2024 at 2:14 pm
Please I do I get the link to install ablebits on my system?
Hello Adesoji,
You can download the latest versions of our add-ins from our Downloads page.
David says:
May 9, 2024 at 2:11 am
I have multiple Excel files contain only 1 sheet "Sheet1".
Say,
Source1.xlsx.Sheet1
Source2.xlsx.Sheet1
Source3.xlsx.Sheet1
I have an existing Excel files (Dest.xlsx) contains 3 sheets, "Source1_data", "Source2_data", "Source3_data".
Can I use your solution to copy the data (with format and formula) in those 3 source files to replace the data in the corresponding sheets in the destination file Dest.xlsx? The file and sheet names are fixed, will not be changed
Lori says:
June 17, 2021 at 10:37 pm
Hello Ablebits Team,
When using "Copy Sheets" and selecting the option "Data from the selected sheets to one sheet" and then selecting the option "Use the same sheet names in other workbooks"...
What is the maximum # of workbooks supported by the add-in?
Thank you!
Hello Lori,
Thank you for contacting us and for your question. Our add-in does not have clearly defined limitations on the number of workbooks to process. However, your resulting worksheet shouldn't exceed Excel limits on the total number of rows and columns on a worksheet.
If you've encountered any difficulties in using our add-in, please let us know how many workbooks you are trying to copy at the same time and how many rows and columns your tables contain. Thank you.
MJ Siddiqui says:
May 7, 2021 at 11:15 pm
I have several files in a folder. All files have same format. How can combine all the files so that a single file is created but the files are not added below each other but next to each other on the final sheet.
I am typing a case below where Col is Column.
File#1: Col A, Col B, Col C, File#2: Col A, Col B, Col C, File#N: Col A, Col B, Col C
Resulting file:
Col A( from Col A, File #1), Col B(From Col B, File #1), Col C(From Col C, File #1) Col D (From Col A, File #2) Col E(from Col B, File#2) Col F(From Col C, File #2) ..... Col N(Col A, From File#N) Col N+1(Col B, From File#N) Col N+2 (Col C, From File#N)
Thanks for all your help.
Hello,
Thank you for your question. When using the Copy Sheets tool, please opt for Copy data from the selected worksheets to one sheet in Step 1 and for Place the copied ranges side by side in Step 3.
If you have any other questions or need further assistance, please feel free to email us at support@ablebits.com. We'll be glad to help.
Ashok kumar says:
December 12, 2020 at 6:20 pm
When I try to select multiple sheets to copy to one sheet, it only shows 5 sheets selected instead of my selection of 19 sheets. What to do. please help.
Hello Ashok,
Thank you for reporting the issue. For us to be able to assist you better, we need to know more details. I have just sent you an email requesting them. Looking forward to your reply.
Viresh Shinde says:
October 5, 2020 at 6:45 am
I want to copy data from multiple sheets to one workbook just one below another.
Hello Viresh,
Thank you for contacting us. The Copy Sheets add-in can help you with this task. Please choose Place the copied ranges one under another on the 3rd step. This add-in is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how the tools work. Here is the direct download link for you: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Sophie says:
June 22, 2020 at 4:23 pm
Hi,
I am trying to copy the same data range from 200 different sheets into one single sheet. Instead of manually selecting the desired range for each sheet, is there a way of standardizing the desired range for all sheets?
Thank you
Hi Sophie,
Thank you for contacting us. I'm really sorry, the add-in doesn't have such an option at the moment. I will forward your request to our development team, hope they would add this functionality in future. Thank you for sharing this suggestion with us.
Ahmed says:
August 1, 2019 at 11:14 am
In copy sheets I want to choose the same range for all sheets, How?
Thank you for your question, Ahmed.
Our Copy Sheets selects the entire table automatically. If you want to change its range, you can do it in the Ranges section on the 2nd step of the add-in, but you'll have to do it manually for each sheet.
Saikumar says:
July 30, 2019 at 5:03 pm
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.....
Thank you for contacting us, Saikumar.
For us to be able to understand your task better, please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the table to 10-20 rows.
If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
We'll look into your task and see if our software can help.
Muhammad Ali Khan says:
May 31, 2019 at 12:33 am
I have used your software . could you explain how I can do this task
- While inserting each row with title, add file name in the first column. (similar to Microsoft Power Query).
Hello Muhammad,
Thank you for contacting us.
Unfortunately, our software doesn't support such functionality. Sorry for not being able to help you more.
mae says:
December 2, 2018 at 2:45 pm
Hi Im trying to copy sheets from the one workbook to a new sheet on the same workbook. The Copy sheet feature doesn't have this option. Will you be able to add this feature? I don't want to create a new workbook. I'm still using a trial version. And I would like this feature before I decide to purchase.
Thank you in advance.
Hi, Mae,
Thank you for contacting us and for your question.
You are correct, the current version of the add-on doesn't have this feature.
I have forwarded your request to our developers and they will look if it is technically possible to implement it. I can't guarantee it will be added, but we can contact you back if/when the functionality is supported.
Thank you for your time and understanding.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.