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. 4 helpful options of the Copy Sheets wizard let 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 the Copy Sheets wizard

Start Copy Sheets

There are two ways to run the tool:

  1. Click on the Copy Sheets icon on the Ablebits Data tab in the Merge group:
    Press the button with the Copy Sheets icon on it.
  2. 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:
    Or use the drop-down menu to select the merging option right away.

Step 1: Choose the way to merge your worksheets

Decide how to copy your worksheets into one:
4 ways to join several Excel files together.

  • Copy sheets in each workbook to one sheet. Place the resulting worksheets to one workbook.
    Choose this radio button to turn the selected workbooks to sheets and get the resulting tables in one file:
    Copy sheets in each workbook to one worksheet and place the resulting sheets 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:
    Combine 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:
    Merge data from the selected worksheets to one sheet.
  • 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.

    Select worksheets to copy.

    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:
    Choose the range to copy manually.

    You can benefit from several additional options at the bottom of the wizard's window:

    • Collapse all / Expand all - press 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 between 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:
    Fine-tune additional options to change the way result will look.

    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 on the rows below data from sheet 1:
      Place the copied ranges one under another.
    • 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:
      Place the copied ranges side by side.
    • 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.
      • Copy tables with their headers. Choose this option to paste ranges together with their headers or unselect it to exclude the top row from the results:
        Pull all table headers into the result.
      • Click Copy and all the data will be placed to a new workbook.

Responses

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.

Reply

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.

Reply
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).

Reply

Hello Muhammad,
Thank you for contacting us.

Unfortunately, our software doesn't support such functionality. Sorry for not being able to help you more.

Reply

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard