VBA Express : Excel - Combine All Workbooks From One Folder

Excel

Combine All Workbooks From One Folder

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

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

Discussion:

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.

Code:

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:

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

Test the code:

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

Sample File:

Combine Worksheets.ZIP 5.28KB

Approved by mdmackillop

This entry has been viewed 502 times.