This is very common situation where user have long list of excel files and want to combine all file into summary workbook.
When you work with multiple Microsoft Excel workbooks, you must often merge the data from two or more workbooks into a summary workbook. This below given procedure (macro code) will help you to combine all excel file into one Excel workbook on single click event or shortcut (whatever command you assign to run your macro 🙂).
1) No fix number of files,
2) Files every times comes with the different names,
3) Size of data is not fixed (No. of rows and columns in worksheets).
Steps: To Combine multiple excel workbooks into one workbook
- Save all files you want to consolidate in one folder
- Open Visual Basic for Editor. Click Develope Tab then under Codes Panel,Visual Basic
- In Visual Basic Editor. Click Insert Tab then Module.
- Copy and Paste below mentioned code in the module
Here is the Macro Code to combine multiple excel workbooks into one
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim smmrywbk As Workbook
Dim wbk As Workbook
Dim ws As Worksheet
Set smmrywbk = ActiveWorkbook
sPath = "C:\ Data\" ‘Change the folder path
sFil = Dir(sPath & "*.xls")
Do While sFil <> ""
strName = sPath & sFil
Set wbk = Workbooks.Open(strName)
Set ws = wbk.Sheets(1)
ws.Range("A1", Range("A" & Rows.Count).End(xlUp)).Select
smmrywbk.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
wbk.Close False ‘Close no save
sFil = Dir
This code will help you to combine multiple excel workbooks into one.