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:
Please contact us here