combine multiple excel sheets into one sheet

(Last Updated On: November 29, 2020)

MERGE MULTIPLE EXCEL FILES WITH MULTIPLE WORKSHEETS

Merging Multiple excel sheets some time could be quite challenging , Especially when we are dealing with large number of file. No matter how many excel sheets we have inside one xls file, we can easily combine them all.

STEPS INVOLVED IN COMBINING MUTIPLE EXCEL FILE

STEP 1: Create a new folder and copy all of the excel files that you want to combine together.

STEP 2: Copy the path of the folder as shown in picture below.

STEP 3: Open Excel and click on new as shown.

STEP 4: Press ALT button and F11 Button together, this will open Visual Basic Editor.

STEP 5: In Visual Basic Editor go to Insert and then click on Module.

STEP 6: Copy the code from below and paste it to Excel visual basic editor

Sub GetSheets()
'Technoholicplus.com Merging excel files
'Please Enter Path of Excel Below

Path = ""

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 Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

STEP 7: Copy the path of the folder as shown in picture below.

STEP 8: Change the path by the copied Path.

Sub GetSheets()
'Technoholicplus.com Merging excel files
'Please Enter Path of Excel Below

Path = "C:\Users\preet\OneDrive\Documents\Excel\"
 

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 Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

STEP 9: Press F5 or Run as Shown Below

Congratulations you have successfully combined all excel file together, Now inspect the newly created file which is ready to use.