Combine multiple excel workbooks into one worksheet of new workbook

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).

combine multiple excel workbooks into one

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
  • Multiple excel workbook consolidate

  • In Visual Basic Editor. Click Insert Tab then Module.
  • Consolidate files into one woekbook

  • 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 &amp; 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



End Sub

This code will help you to combine multiple excel workbooks into one.

January 17, 2013

2 Responses on Combine multiple excel workbooks into one worksheet of new workbook"

  1. Hi Sonu, thanks for contacting ExcelCrazy.

    If you are looking for Index formula then check below link,

  2. sonu says:

    You should post one more thing that HOW TO MAKE INDEX and how to connect it to worksheets.

Leave a Message

Your email address will not be published. Required fields are marked *

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Location (required)

Your Message for us...