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
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Merging multiple tabs into 1 tab:
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
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
Range("A1").Select
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)
Next
End Sub
Hi everyone,
First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.
I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.
In Addition to this I have to merge all the worksheets and have them in another sheet which is called “Übersicht” (Overview). However there is a different column in the sheet and it’s between “Nr.” and “Thema” columns (which are in A1 and A2 in all the 9 Sheets) and this different column called “Kategorie” (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this “Nr. (A1), Kategorie (A2) and Thema (A3)…..”.So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in “Übersicht” (Overview) sheet needs to update by itself. How can I do this?
P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.
I hope I explained it well. Thanks a lot in advance!
I wish you merry Christmas and a happy new year!
oduff
Thanks for sharing your code.
If the first cell of any row is empty then it seems your code is skipping that row
eg: R1C1 – empty
R1C2 – value
R1C3 -value
I don’t like empty first cells 🙂
Brilliant code – I have attempted to try a number of different codes with no luck and this works perfectly – thank you so much!
Hi, the code is great but is it possible to add a ‘tweak’? I want to merge multiple worksheets from within the same workbook. Every worksheet has the first row as column headers but … the columns are not always in the same order and some worksheets have more columns than others because additional data fields have been added over the years. Is it possible to have the worksheets when merging to compare it’s column heading with the Master column heading and re-order the columns if needed with any new columns added? Thank you
Maybe you can try something like https://openrefine.org/ to restructure all the data?
how to combine excel sheets in a workbook if all row headers are the same (not column headers)
I am trying to run on macbook pro and my path looks like this:
/Users/spudn56/Desktop/split100
I tried tweaking your path but had no success. Any idea’s I am not a programmer so learning as I go.
good code. i’ve been trying to add just adding select tabs from a workbook.
Sheet.Copy After:=ThisWorkbook.Worksheets(“WorksheetName”) instead of
Sheet.Copy After:=ThisWorkbook.Sheets(1)
but that doesn’t seem to work. any suggestions?