Copy several worksheets into one

How to join several sheets in Excel 2016-2007

Consolidate Worksheets Wizard gives you several options to copy data from multiple sheets into one without copying and pasting. You can turn workbooks to worksheets and copy the resulting sheets to one file. It's also possible to join worksheets with the same name, copy sheets to one book, or get data from the selected ranges in one list.

See also: Merge Excel Tables wizard will help you combine data from two Excel sheets by matching columns in seconds!
Free Download
7-day fully functional version

Copy multiple Excel worksheets into one

When you work with multiple sheets located in different Excel files, you may need to bring certain records together. Feel free to use one of 4 helpful options of the Copy Sheets tool that lets you paste data from several spreadsheets to one list or workbook.

Step 1: Specify the way you want to copy the data

  1. Click on the Copy Sheets icon in Excel to run the wizard.
  2. Choose 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 turn the selected workbooks to sheets and then get the resulting tables in one file. Copy sheets in each workbook to one sheet. Place the resulting worksheets to one workbook
    • 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 data from the selected sheets with the same name to one sheet
    • Copy the selected worksheets to one workbook. Paste the needed sheets from different Excel files to one book. Copy the selected worksheets to one workbook
    • Copy data from the selected worksheets to one sheet. Use this option to create one table with data from multiple sheets. Copy data from the selected worksheets to one sheet
      Tip. Click on the down arrow next to the add-in icon in the toolbar and pick the necessary option in the drop-down list to go right to step 2. Select the way to copy multiple sheets right from the toolbar

    Step 2: Select the data to copy

    You will see a full list of files open in Excel on this step. Tick off the check-boxes next to the names of the worksheets you want to copy.

    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.

    Click the Select all checkbox to pick all sheets at a time

    By default, the add-in pulls all data ranges from the sheets. However, you can change the selection by using the Select range icon next to the sheet name in the list. It will show a dialog box allowing you to pick the correct cells.

    You can benefit from several additional options at the bottom of the window.

    • Expand all / Collapse all – press this button to quickly show or hide 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.

    Step 3: Choose how to paste the data

    This step lets you specify how you want to copy and paste the records into the resulting sheet:

    • Paste all. If the copied ranges contain formulas, then Excel formulas will be pasted 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.
    • Place the copied ranges one under another. Paste the copied ranges vertically, i.e. data from sheet 2 will appear on 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.
    • Preserve formatting. Tick off this checkbox to keep the 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.
    • Copy tables with their headers. Tick off this option to paste ranges together with their headers, or unselect it to exclude the top row from the results.
    Click on the Select range icon

    Click Copy to see a new workbook with the copied data.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
Excel add-ins and Outlook tools - Ablebits.com