While this can be done manually, it would be time-consuming and error-prone. However, a simple VBA code can do this in a few seconds.
Combine Multiple Excel Files into One File
Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook:
How to Use this Code?
Here are the steps to use this code:
Put all the Excel files that you want to combine into a folder. For the purpose of this tutorial, I have created a folder named Test and have six files in it (4 Excel workbooks and 1 Power Point and Word each). Open a new Excel workbook. Press ALT + F11 (or go to Developer –> Code –> Visual Basic). This will open the Visual Basic Editor. In the VB Editor, in the Project Editor, right-click on any of the objects for the workbook and go to Insert –> Module. This will insert a module for the workbook. Double click on the module. It will open the code window on the right. Copy and paste the above code into the code window. In the code, you need to change the following line of code: FolderPath = Environ(“userprofile”) & “DesktopTest” In this line, change the part in double quotes (highlighted in orange) with the location of the folder in which you have the files that you want to combine. In the code used above, the folder is on the Desktop. In case you have it in some other location, specify that path here. Place the cursor anywhere in the code and click on the green play button in the Toolbar options (or press the F5 key).
This will run the code and all the worksheets from all the Excel files in the folder would get consolidated into a single workbook.
How this Code Works?
The code uses the DIR function to get the file names from the specified folder. The following line assigns the first excel file name to the variable ‘Filename’. Filename = Dir(FolderPath & “.xls”) Then the Do While loop is used to check whether all the files have been covered. Within the ‘Do While’ loop, ‘For Each’ loop is used to copy all the worksheets to the workbook in which we are running the code. At the end of the Do Loop, following line of code is used: Filename = Dir(). It assigns the next Excel file name to the Filename variable and the loop starts again. When all the files are covered, DIR function returns an empty string, which is when the loop ends.
Here is an explanation of the DIR function in the MSDN library: Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (“”). Have you ever tried something of this sort using VBA? Do share what you did and we all can learn from it. Save Crazy Amount of Time Using VBA. Check out the Excel VBA COURSE.
How to Combine Data from Multiple Workbooks into One Excel Table (using Power Query). Quickly Create Summary Worksheet with Hyperlinks in Excel. How to Create and Use an Excel Add-in. How to Run a Macro. 20 Useful Excel Macro Examples.
- Save the document as .xlxm 2. Make sure you have a “/” at the end of your directory path When you modify the FolderPath = Environ(“userprofile”) & “DesktopTest” make sure you include the pre and post forward slashes “” to the folder path containing the excel sheets to be imported. When you are debugging right click on the Folderpath and Filename variable and set them to the Watchlist with the Break when value changes box checked. When you run the program you can verify those two variables are updating correctly. FolderPath = Environ(“userprofile”) & “DesktopTest” Environ(“userprofile”) will only provide the first part of a filepath. In my case it was: C:UsersWF. There were several subfolders “along the path” before getting to the folder I used for the files to be combined. Use this example as the path to the correct folder: folderpath = “C:UsersWFDocumentsFolder1Folder2Folder3FolderWithFiles For a convenient way to get the correct path use this code to place the path on a worksheet. Then copy/paste into the sub procedure. Sub Path_FileName() Dim strPath As String strPath = ActiveWorkbook.FullName ActiveCell.Value = strPath End Sub Make this adjustment if you need to. This is some very useful code. “DESKTOP TEST” = LOCATION OF FILE I changed this string of code (moved the parenthesis) from the below and it worked: CHANGED FROM FolderPath = Environ(“userprofile”) & “DesktopTest” CHANGED TO FolderPath = Environ(“userprofile” & “DesktopTest”) Any solution Is it possible to name the sheets accordingly to their filenames? But it create multiple sub sheets of multiple files…what should to do if we need all multiple files into only one single sub sheet and i also want that the data copy should be in sequence Debug = Sheet.Copy After:=ThisWorkbook.Sheets(1) Data copy from below listed excels Excel 1= Los Angeles CCAP ASE Job Log Backup 15-11-18 Excel 2= Anaheim CCAP ASE Job Log Backup 15-11-18 Excel 3= BAY-North CCAP ASE Job Log Backup 15-11-18 Excel 4= San Diego CCAP ASE Job Log Backup 15-11-18 Now from every file, I have to copy only 1 sheet. names of sheets are mentioned below. Excel 1 sheet name= LA Excel 2 sheet name= Anaheim Excel 1 sheet name= SD Excel 1 sheet name= BayNorth Please help I have to submit a report to my manager. This works good if you want to combine multiple sheets in 1 workbook. I need a solution where the following steps need to be done :
- There are around 4000 employees and the workbook will be each employee code wise 2. There are 18 folders with workbooks with 1 worksheet for each employee.. there is a possibility that for an employee one of the workbooks will not be there 3. The macro should create a workbook with the emp code then across the 18 folders should check whether workbook is available for the employee ID and if yes 4. Copy and paste it in the created workbook and save. 5. Additionally there are 3 workbooks by default which need to be added for all employees What i do not know to edit the above macro is:
- How do i build a For loop for an employee list 2. If there is no specific excel workbook in one of the folder then it should not throw an error Please help. I can communicate on your official email id too and will be happy if you can provide a solution at the earliest even if it is chargeable. Regards Hope these help you all 🙂 TL;DR I don’t know VBA & this formula didn’t work in my scenario. How can I modify this script to combined Sheets with the name “Sheet1” from multiple file into one file. If Rectify This Then It Automatically Works When pressing “F5” But it create multiple sub sheets of multiple files…what should to do if we need all multiple files into only one single sub sheet………… Pls. help. Firstly Thanks for concern. I have send an email to you for further clarification on it. Pls. have a look. Pls. help on this…………
