Quickly merge Excel files, spreadsheets or tabs with VBA

Sometimes you just need to merge hundreds of Excel files into one. For CSV files you can easily use the Command Prompt, for Excel you’ll need some VBA scripting. Working with many individual files can be time consuming so use below VBA scripts to merge individual XLS-files into one or combine multiple tabs into one sheet. Simply go to the Visual Basic Editor (Alt / Fn + F11), add a new module and run the script. An easy to understand tutorial can be found at The Excel VBA editor.

Merge Excel files into one
Use the following VBA code for merging multiple XLS files into 1 worksheet, change to the correct folder where you have saved all individual Excel files:
Sub GetSheets()
Path = "\\notprovided\clients\bestclientever\SEO\keyword-lists\"
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
Filename = Dir()
End Sub

Merging multiple tabs into 1 tab:

Sub Combine()
Dim J As Integer
On Error Resume Next
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
End Sub

Leave a Comment.