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
Option Explicit Sub Combination() 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 Range(Selection, Selection.End(xlToRight)).Copy smmrywbk.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues wbk.Close False 'Close no save sFil = Dir Loop smmrywbk.Save End Sub
This code will help you to combine multiple excel workbooks into one.