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:

  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