Combine multiple excel workbooks into one worksheet of new workbook

Share Now

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.

Share Now
November 15, 2019

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

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

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

Social Media

facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


0124- 4601426


EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED