#MergeFiles | Explore Tumblr posts and blogs

professorexcel · 6 years ago

Text

Merge Excel Files: How to Combine Workbooks into One File

You got several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 6 different methods of how to merge existing workbooks and worksheets into one file. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them.

Method 1: Copy the cell ranges

Copy and paste the source content into your main workbook.

The obvious method: Select the source cell range, copy and paste them into your main workbook. The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges. On the other hand: For just a few ranges it’s probably the fastest way.

Method 2: Manually copy worksheets

Copy worksheets separately to the “master” workbook.

The next method is to copy or move one or several Excel sheets manually to another file. Therefore, open both Excel workbooks: The file containing the worksheets which you want to merge (the source workbook) and the new one, which should comprise all the worksheets from the separate files.

Select the worksheets in your source workbooks which you want to copy. If there are several sheets within one file, hold the Ctrl key and click on each sheet tab. Alternatively, go to the first worksheet you want to copy, hold the Shift key and click on the last worksheet. That way, all worksheets in between will be selected as well.

Once all worksheets are selected, right click on any of the selected worksheets.

Click on “Move or Copy”.

Select the target workbook.

Set the tick at “Create a copy”. That way, the original worksheets remain in the original workbook and a copy will be created.

Confirm with OK.

.aq_block_15138 h1 { color: ; } .aq_block_15138 p { color: ; }

(adsbygoogle = window.adsbygoogle || []).push({});

Method 3: Use the INDIRECT formula

The next method comes with some disadvantages and is a little bit more complicated. It works, if you got your files in a systematic file order and just want to import some certain values. You build your file and cell reference with the INDIRECT formula. That way, the original files remain and the INDIRECT formula only looks up the values within these files. If you delete the files, you’ll receive #REF! errors.

With the INDIRECT formula you can link to other files. Only condition: the source file must be open in the background. On the right hand side: The original file. On the left hand side: The main/ combined workbook.

Let’s take a closer look at how to build the formula. The INDIRECT formula has only one argument: The link to another cell which can also be located within another workbook.

Copy the first source cell.

Paste it into your main file using paste special (Ctrl + Alt + v ). Instead of pasting it normally, click on “Link” in the bottom left corner of the Paste Special window. That way, you extract the complete path. In our case, we got the following link:

=[160615_Examples.xlsm]Thousands!$C$4

Now we wrap the INDIRECT formula around this path. Furthermore, we separate it into file name, sheet name and cell reference. That way, we can later on just change one of these references, for instance for different versions of the same file. The complete formula looks like this (please also see the image above):

=INDIRECT("'"&$A3&$B3&"'!"&D$2&$C3)

Important – please note: This function only works if the source workbooks are open.

.aq_block_23038 h1 { color: ; } .aq_block_23038 p { color: ; }

Excel too slow? Speed it up. Get the book now!

.aq_block_63038 h1 { color: ; } .aq_block_63038 p { color: ; }

Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!

Learn how Microsoft Excel performs calculations

Use the simple and effective step-by-step guide to master each method

Get to know the impact each method will have on performance

Learn more or get it on Amazon!

Method 4: Merge files with a simple VBA macro

You are not afraid of using a simple VBA macro? Then let’s insert a new VBA module:

Go to the Developer ribbon. If you can’t see the Developer ribbon, right click on any ribbon and then click on “Customize the Ribbon…”. On the right hand side, set the tick at “Developer”.

Click on Visual Basic on the left side of the Developer ribbon.

Right click on your workbook name and click on Insert –> Module.

Copy and paste the following code into the new VBA module. Position the cursor within the code and click start (the green triangle) on the top. That’s it!

Sub mergeFiles() 'Merges all files in a folder to a main file. 'Define variables: Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet Set mainWorkbook = Application.ActiveWorkbook Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker) 'Allow the user to select multiple workbooks tempFileDialog.AllowMultiSelect = True numberOfFilesChosen = tempFileDialog.Show 'Loop through all selected workbooks For i = 1 To tempFileDialog.SelectedItems.Count 'Open each workbook Workbooks.Open tempFileDialog.SelectedItems(i) Set sourceWorkbook = ActiveWorkbook 'Copy each worksheet to the end of the main workbook For Each tempWorkSheet In sourceWorkbook.Worksheets tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) Next tempWorkSheet 'Close the source workbook sourceWorkbook.Close Next i End Sub

Method 5: Automatically merge workbooks

Easily merge Excel files with Professor Excel Tools

The fifth way is probably most convenient:

Press the Merge Files button within the ‘Professor Excel’ ribbon.

Now select all the files you want to merge and all worksheets of these Excel files will be copied to the current workbook.

This procedure works well also for many files at the same time and is self-explanatory.

But you need a third party add-in, for example our ��Professor Excel Tools“. You can download and use it for 7 days for free.

.aq_block_61635 h3 { color: ; } .aq_block_61635 p { color: #2b803f; }

This function is included in our Excel Add-In 'Professor Excel Tools'

.aq_block_71635 h3 { color: ; } .aq_block_71635 p { color: #2b803f; }

Learn more Download Free Trial (No sign-up, download starts directly) More than 10k people on Facebook can't be wrong.

(New) Method 6: Use the Get & Transform tools

The current version of Excel 365 offers the “Get & Transform” tools to import data. These functions are very powerful and are supposed to replace the old “Text Import Wizard”. However, they have one useful feature: Import a complete folder of documents.

The requirements: The workbooks and worksheets you want to import have to be in the same format.

Please follow these steps for importing a complete folder of Excel files.

Create a folder with all the documents you want to import.

Usually it’s the fastest to just copy the folder path directly from the Windows Explorer. You still have the change to later-on select the folder, though.

Within Excel, go to the Data ribbon and click on “Get Data”, “From File” and then on “From Folder”.

Paste the previously copied path or select it via the “Browse” function. Continue with “OK”.

If all files are shown in the following window, either click on “Combine” (and then on “Combine & Load To”) or on “Edit”. If you click on “Edit”, you can still filter the list and only import a selection of the files in the list. Recommendation: Put only the necessary files into your import folder from the beginning so that you don’t have to navigate through the complex “Edit” process.

Next, Excel shows an example of the data based on the first file. If everything seems fine, click on OK. If your files have several sheets, just select the one you want to import, in this example “Sheet1”. Click on “OK”.

That’s it, Excel now imports the data and inserts a new column containing the file name.

For more information about the Get & Transform tools please refer to this article.

Der Beitrag Merge Excel Files: How to Combine Workbooks into One File erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/2Phpjws

#Excel #Professor Excel

0 notes