This video demonstrates how to combine all Excel workbooks in a folder into a new workbook using VBA. All sheets in each workbook will be copied to a new workbook. The VBA code featured in the video… You have 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. Contents
SummaryIf you want to merge just a small amount of files, go with methods 1 or method 2 below. For anything else, please take a look at the methods 4 to 6: Either use a VBA macro, conveniently use an Excel-add-in or use PowerQuery (PowerQuery only possible if the sheets to merge have exactly the same structure). Method 1: Copy the cell rangesCopy 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 worksheetsCopy 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.
One small tip at this point: You can just drag and drop worksheets from one to another Excel file. Even better: If you press and hold the Ctrl-Key when you drag and drop the worksheets, you create copies. Do you want to boost your productivity in Excel? Get the Professor Excel ribbon! Add more than 120 great features to Excel! Method 3: Use the INDIRECT formulaThe next method comes with some disadvantages and is a little bit more complicated. It works, if your files are 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.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.
Important – please note: This function only works if the source workbooks are open. Method 4: Merge files with a simple VBA macroYou are not afraid of using a simple VBA macro? Then let’s insert a new VBA module:
Method 5: Automatically merge workbooksThe fifth way is probably most convenient: Click on “Merge Files” on the Professor Excel ribbon. Now select all the files and worksheets you want to merge and start with “OK”. This procedure works well also for many files at the same time and is self-explanatory. Even better: Besides XLSX files, you can also combine XLS, XLSB, XLSM, CSV, TXT and ODS files. To do that you need a third party add-in, for example our popular “Professor Excel Tools” (click here to start the download). Here is the whole process in detail: Just click on “Merge Files” on the Professor Excel ribbon, select your files and click on OK.Method 6: Use the Get & Transform tools (PowerQuery)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.
For more information about the Get & Transform tools please refer to this article. Next step: Merge multiple worksheets to one combined sheetAfter you have combined many Excel workbooks into one file, usually the next step is this: Merge all the imported sheets into one worksheet. Because this is a whole different topic by itself, please refer to this article. Image by MartinHolzer from Pixabay How do I combine multiple Excel files into one workbook?Open the original Shared Workbook into which you want to merge changes. Click the Tools menu and then select Merge Workbooks…. If prompted, save the workbook. In the file navigation dialog box, click the copy of the workbook that contains the changes you want to merge, then click OK.
How do I consolidate worksheets in Excel VBA?Merging all sheets of active workbook into one sheet with VBA. Activate the workbook you want to combine its all sheets, then press Alt + F11 keys to open Microsoft Visual Basic for Applications window.. In popping window, click Insert > Module to create a new Module script.. Copy below code and paste them to the script.. How do you merge multiple sheets with same headers in Excel VBA?Consolidate excel sheets with same headers by Consolidate function. Select the calculation you want form Function list;. Click to select the range you want to merge.. Click Add button to add the range into All reference list. ... . Check Top row and Left column under Use labels in section.. |