How to merge multiple Excel files into one
How to merge multiple Excel files into one: Do you often need to consolidate and summarize reports, lists, or data from different departments into a single file? Copying and pasting manually can be time-consuming and may lead to errors when dealing with large amounts of data. How can you consolidate data accurately and quickly? This article will guide you on how to merge multiple Excel files into one. You can use this method to consolidate work results, reconcile income and expenses of your organization within any period of time.
Example: Let’s say we have 4 Excel files in the “Merge files” folder as shown in the image below. We need to merge all 4 Excel files into a single file. To do this, please follow the steps below.
How to merge multiple Excel files into one
Step 1
First, open a new Excel file. Then select the Developer tab on the ribbon. Next, select Visual Basic under the Code section. Alternatively, you can use the shortcut key Alt + F11 to open the VBA window.
How to merge multiple Excel files into one
Step 2
At this point, the Microsoft Visual Basic for Applications window appears. Select the Insert tab on the ribbon. Then, choose the Module option from the scrolling menu.
How to merge multiple Excel files into one
Step 3
Now, the Module dialog box appears. Copy the following code into the Module dialog box.
Sub copy()
Path = "C:\Users\admin\OneDrive\Desktop\Gop file\"
Filename = Dir(Path & "*.xls*")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.copy after:=ThisWorkbook.Sheets(1)
Next
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Then click Run on the ribbon or press the F5 key to run the code.
How to merge multiple Excel files into one
Note that in the line “Path = “””, the text within the quotation marks is the path of the folder where you have saved the Excel files. Make sure to add a trailing backslash “\ ” after the path to indicate it is a folder.
How to merge multiple Excel files into one
That’s it! All Excel files in the “Merge files” folder have now been merged into a single Excel file. You can now make any necessary modifications to the data in the new Excel file and click Save to save this file.
How to merge multiple Excel files into one
In this article, we have demonstrated how to use VBA code to merge multiple Excel files into a single file. We hope this article will be helpful to you in your work. Good luck!
