Merge excel files

Analyst Cave

Merge Excel files – How to merge multiple Excel files

Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that – how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in.

Merge Excel files manually

Open the destination Workbook

Merge Source.xlsx with Destination.xlsx Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx).

The further steps need to be repeated for each Excel Workbook you want to copy to the destination Workbook.

Open (each) the Workbook you want to merge and copy Worksheets

Copy each Worksheet to the destination Workbook Open each Excel Workbook you want to merge with the destination Workbook.

Next right-click on each Worksheet you want to copy, click Move or Copy. . In the Move or Copy Window select the destination Workbook (Destination.xlsx in our case). Next:

If you want to move (cut & paste) the Worksheet – simply click the OK button to proceed
If you want to copy (copy & paste) the Worksheet – select the Create a Copy checkbox and click the OK button

Copy Worksheet to destination Workbook
Repeat Step 2 for each Workbook you want to copy to the destination Workbook.

Merge Excel files using VBA

Let us assume we have a couple of files listed in our directory (in my example these are File1 and File2.xlsx). What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook. I wanted however to account for 2 typical scenarios:

Copy ALL worksheets
Copy only a single worksheet – with a specific name

The code below supports both these scenarios.

Merge Excel files code

Use the MergeExcelFiles Sub procedure below to merge any number of Workbooks:

How to use the procedure above? Below I create a simple Test procedure that lists the Excel files within the Workbook directory and merges the Workbooks.

Pretty simple right?

Merge Excel files within a directory

The scenario above works pretty well for situations where we want to list explicitly files we want to merge into a single Excel Workbook. How about when we have tons of files? Or to make it more simple, if we want to merge all files within a SINGLE directory. As this is also a typical scenario I have modified the above Test procedure to accommodate just that:

What happens is that we use the VBA Dir function to loop through all files within a selected directory. As we go along we modify our VBA Array redefining its size and adding additional items.

Download the code

Want to download the code above and the example?

Merge Excel files in a whole directory structure

One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge. As the VBA Dir function works only for a single directory and does not traverse any directories within we would need to amend additionally the code above. If needed I recommend reading my post on How to traverse directories using the VBA Dir function.

How to Merge Excel Files and Sheets

Sometimes the Microsoft Excel data you need is split across multiple sheets or even multiple files. It can be significantly more convenient to have all of this information in the same document.

In a pinch, it’s possible to copy and paste the various cells you require, placing them all in the same sheet. However, depending on how much data you’re working with, this might end up taking a lot of time and effort.

Instead, consider some of the smarter ways to accomplish the same task. These methods might just allow you to skip some of the busywork when it comes to merging sheets or files in Excel.

How to Merge Sheets in Excel

Excel makes it easy to combine more than one sheet into a new workbook:

Open the sheets you want to merge.
Click Home >Format >Move or Copy Sheet.
Use the dropdown menu to select (new book).
Click OK.

Merge Excel Sheets Into One File

The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method has its limitations, but it’s quick and straightforward.

First, open up the sheets you want to merge into the same workbook. From there:

Head to Home
Click Format
Select Move or Copy Sheet

Читать еще: Прогнозирование временного ряда в excel

You should see controls for where to move the selected sheets and the order for those sheets.

Use the dropdown to select (new book). This will serve as the master spreadsheet where you send all of your individual sheets. You can use the Before sheet box to specify the order that the sheets are in.

Repeat this process with the rest of the sheets you want to merge. Then save your new master document.

Merge Excel Data Into One Sheet

Sometimes, you might want to take more than one dataset and present it as a single sheet. This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time.

There are two important conditions for this process to work correctly. First, the sheets that you’re consolidating need to use exactly the same layout Use Custom Views in Excel to Save Specific Worksheet Layouts Excel’s Custom View setting makes it easy to view specific information on a crowded spreadsheet or to create different layouts for your data. Read More , with identical headers and types of data. Second, there can’t be any blank rows or columns.

When you’ve arranged your data to those specifications, create a new worksheet. It’s possible to run the consolidation procedure in an existing sheet where there’s already data, but it’s easier not to.

In this new sheet, head to the Data tab and click Consolidate. Select Sum from the dropdown and then use the button in the Reference field to access your spreadsheet so you can select the data you need.

To add the next dataset, click Add and then select the data in the same way. Do this for all the datasets that you want to merge. You can even draw from other workbooks by using the Browse button, which is Select in the Mac version of Excel.

Check the box titled Create links to source data if you’re going to continue to update the data in other sheets and want this sheet to reflect that. You can also select which labels are carried across with the Use labels in checkboxes, as shown above.

Finally, click OK.

Combine Excel Files With VBA

If you want to combine sheets from several workbooks in a snap, the best way is to write a simple VBA macro. This will come in especially handy if you perform this task on a regular basis.

First, make sure that all the files you want to combine are in the same folder on your computer. Then, create a new Excel spreadsheet that will bring them all together.

Head to the Developer tab and select Visual Basic. Click Insert > Module.

Copy and paste the following code, taken from a guide by ExtendOffice:

Make sure to change the path to wherever the files are stored on your computer.

Next, save your workbook as an XLSM file so that macros are enabled. Then run the macro and you should find that you have a single workbook that contains all the sheets from all the files in the folder.

Take Caution Before Merging Excel Data

Merging sheets and files in Excel can be rather complicated and messy. This illuminates one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.

Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time. When you start working with a new workbook, it’s best to consider all possibilities of how the file will be used further down the line.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Merge excel files

Hi
I tried method 4 (VBA) and it works fine for a simple merge.
But what I want is to update the merge task withou duplicate sheets.
Explaining:
I have three large files updating in a daily basis (each one with one sheet). Then i need to merge all of them into one evaryday.
Using your method 4 for second time, it duplicates sheets, instead of replacing the existing sheets.

So, it would be nice if you provide the changes needed in your code to do the update and, if possible, to execute automatically every 24 hours.

Thank You very much for your time
JT

Читать еще: Подсчет количества одинаковых значений в excel

i want to combine a specific sheet from the source files to a work book. Not all the work sheets in the source files.Suppose three product files , (product1 , product2 and product3) all the files has many sheets like sales,employees, expenses and so on. But i need only sales sheets from all the 3 product files into one work book (not in one work sheet)

Henrik Schiffner

Hi Hari,
I’m working on an advanced merge function for my add-in. But it’ll take some time.
Best regards,
Henrik

Hi
when i used vba code it worked fine but i want all the data to merged in one single worksheet instead of seperate worksheets in one workbook.

Henrik Schiffner

Hi VVS,
Please check out this article: https://professor-excel.com/merge-sheets/. After merging the data into one workbook (on separate sheets), you can copy them underneath each other on one single worksheet.
Best regards,
Henrik

Hi Henrik,
Instead of running the code twice is there any chance that merging the data from different works books into one single workbook on one tym running the vba code .

What if you aren’t merging workbooks (.xlsx), but other data files saves as .csv, or .txt, or .dat .. can the VBA script handle those?

sherazad

Thanks very much for your code, I tried it, it works but I have this error message: Run-time error ‘1004’ Method ‘Copy’ of object ‘Worksheet’ failed, could you please help me on this.

Hi Henrik, the code corked fine. now ,i want to delete the header rows in the merge file when it is executed and should display only final header row at the top but not all the header rows.

I have multiple workbooks with multiple worksheets (same columns for all workbooks, but different columns within each workbook ie. both book1 and book2 have sheet1 and sheet2, or more). Your code adds ALL sheets one after another, so I end up having 4 tabs in the output. Can you tweak it so sheet1 from book1 and book2 are merged in one new sheet, and sheet2 from book1 and book2 are merged in another sheet (2 sheets 1 workbook in the output)? Thanks!

Renato

thank you very much for the VBA code.
Could you please help me a bit related to that code?
I should copy only the 2nd, 3rd and 4th sheets from each excel workbook into a separate one. How should I change the code to be able to avoid the remaining tabs?

Thank you,
Renato

I seriously love you for posting #4 (with the adjustment in your comment reply. Instead of requesting more customization I’ll tell you a story. I’ve been on a self-directed crash-course in VBA since stepping into an analyst role two weeks ago, tasked with (among other things) collecting and tracking responses for procedural non-compliance from a few hundred field units. Old method: field offices were sent a 32-column data export and attempted to copy/paste the rows which apply to their office into a separate “Response Form” (aka a Word doc) along with additional identifying info and investigation/action details. The analyst tracked responses by highlighting lines on a pivot table, which was challenging because few field units could successfully copy/paste 32 cells or provide all requested info or type a Subject line that distinguished one response from 100 others. My method: Turned the Word Doc into a 32-column worksheet, merged it with the data export, added a Double-Click event function so *anyone* can fully populate their response form directly from the data export tab; gave the form a 1-click “Submit” function which references identifying data (via their double-click event) to rename the worksheet which then duplicates and emails itself to me with a subject line that also references identifying data; found Outlook VBA that extracts all attachments from the folder which their responses (with those uniform and predictable subject lines) are filtered to, to an Import-folder on my desktop; and a tracker column on my master data export which matches identifying info from the response sheets as I import them into the tracking worksheet. The ONLY thing missing has been a convenient way to batch-import those attachments into the tracker without requiring an Add-In (miles of red tape to get one approved). I knew it could be done but was unable to find an answer for the past two weeks before stumbling onto this post. You’re a God-send at the end of a 13-hour day. Thank you.

Number 6 works brilliantly! Thanks!

Hi
I try to merge 300 excel files. Although I use the method 5, when I combine sheets I have got some problem. the problem is I have got billion cell at end of my first sheet’s number … what I can do with this cells.
The problem gone when I save the excel sheets one more time , but I dont want to ( save as) all my 300 excel files again.

Читать еще: Как узнать кем открыт файл excel

Is it possible to name the sheets accordingly to their filenames?

every ten minutes i’ll recieve and excel file how can i merge this file which is been recived every ten minutes to a new excel file automatically ?

aleshia Carr

I have 70 workbooks with multiple sheets, I only want a specific sheet from each workbook, how can I tweek the code in #4 to accomplish this?

I used method 6, and it worked amazingly. That saved me about 3 hours of manually copy and pasting 6 months of daily files into a single sheet. Thank you so much for the instructions, very VERY much appreciated.

Mail merge using an Excel spreadsheet

Here are some tips to prepare your Excel spreadsheet for a mail merge. Make sure:

Column names in your spreadsheet match the field names you want to insert in your mail merge. For example, to address readers by their first name in your document, you’ll need separate columns for first and last names.

All data to be merged is present in the first sheet of your spreadsheet.

Data entries with percentages, currencies, and postal codes are correctly formatted in the spreadsheet so that Word can properly read their values.

The Excel spreadsheet to be used in the mail merge is stored on your local machine.

Changes or additions to your spreadsheet are completed before it’s connected to your mail merge document in Word.

You can import information from your Excel spreadsheet by importing information from a comma-separated value (.csv) or a text (.txt) file and use the Text Import Wizard to build a new spreadsheet.

Connect and edit the mailing list

Connect to your data source. For more info, see Data sources you can use for a mail merge.

Edit your mailing list

Choose Edit Recipient List.

In Mail Merge Recipients, clear the check box next to the name of any person who you don’t want to receive your mailing.

Note: You also can sort or filter the list to make it easier to find names and addresses. For more info, see Sort the data for a mail merge or Filter the data for a mail merge.

You can insert one or more mail merge fields that pull the information from your spreadsheet into your document.

To insert merge fields on an envelope, label, email message, or letter

Go to Mailings > Address Block.

To add a greeting line, choose Greeting Line.

To add other merge fields, like invoice numbers, see Insert mail merge fields.

Choose File > Save.

if you’re interested in learning more about options for setting up email message, see Email merge in Word.

After you insert the merge fields you want, preview the results to confirm that the content is okay. and then you’re ready to complete the merge process.

Go to Mailings> Preview Results.

Choose Next or Previous to move through records in your data source and view how they appear in the document.

Go to Finish & Merge > Print Documents or Send E-mail Messages.

When you save the mail merge document, it stays connected to your data source. You can reuse the mail merge document for your next bulk mailing.

Open the mail merge document and choose Yes when Word prompts you to keep the connection.