Combine All Workbooks From One Folder

Ease of Use


Version tested with


Submitted by:

Jacob Hilderbrand


This macro will copy all the worksheets from all the workbooks in one folder into the active workbook. 


Suppose you have several workbooks each with one or more sheets and you want to put all the worksheets from all the workbooks into one workbook. This macro does all the work for you. 


instructions for use


Option Explicit Sub CombineFiles() Dim Path As String Dim FileName As String Dim Wkb As Workbook Dim WS As Worksheet Application.EnableEvents = False Application.ScreenUpdating = False Path = "C:\" 'Change as needed FileName = Dir(Path & "\*.xls", vbNormal) Do Until FileName = "" Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName) For Each WS In Wkb.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Next WS Wkb.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code in the Code Window that opens up.
  5. Change the Path as needed.
  6. Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

  1. Tools | Macro | Macros...
  2. Select CombineFiles and press Run.

Sample File:

Combine Worksheets.ZIP 5.28KB 

Approved by mdmackillop

This entry has been viewed 491 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express