VBA Macro For Merging WorkbooksHere is a five-step guide to combine all sheets of different workbooks to a single workbook. Follow these steps as mention: -
1. First of all put all the workbooks that you want to combine into the one folder as shown in screenshot:-
2. Launch an Excel file that you want to combine other workbooks into.
3. Press ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window or you can open Microsoft Visual Basic window from developer tab >>Macros (in Code Group) as shown picture. (step by step guide about to add developer tab Click here)
4. Copy the following VBA and Click Insert > Module, and paste the following code in the Module Window and change the path from “C:\Users\ankit\Desktop\EWE” to the path of the folder in which all the files have been moved/copied.
Path = "C:\Users\ankit\Desktop\EWE\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Filename = Dir()
5. Now press F5 key to run this code, and it’s all done. All of the worksheets (including the blank worksheets) within the workbooks have been merged into the active workbook.
- The workbook in which you want to combine all the worksheets and all other workbooks must not be in one folder. (Tip:- use this macro in unsaved new workbook)
- This VBA code can merge the different workbooks into the single workbook, if you want to combine specified worksheets of the workbook then <<click here>>.