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.

Video Version

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.

ms excel

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

excel

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.

vb editor

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.

Combine Multiple Excel Sheets

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

Combine Multiple Excel Sheets

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

Avatar

Pritam Singh Negi

Hi, My name is Pritam, I love to help everyone, That is the sole purpose of this blog. Helping people in troubleshooting small things, so that they don't bother about them. Moreover, I love to talk about technology, If you do then join me at our facebook page https://www.facebook.com/technoholicplus.