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

[code language=”vb”]
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. sonu says:

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

  2. Hi Sonu, thanks for contacting ExcelCrazy.

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

Leave a Message

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