How do I combine multiple Excel files into one spreadsheet using VBA?

This video demonstrates how to combine all Excel workbooks in a folder into a new workbook using VBA.  All sheets in each workbook will be copied to a new workbook.

Click here to Enrol on the Ultimate Excel VBA Course
30+ hrs - includes certification.

All purchases help to support this blog - thanks!

The VBA code featured in the video…

Sub CombineWorkbooks()

Dim Path As String
Path = "C:\Users\Blue Pecan\Desktop\Combine Workbooks Example\"

Dim FileName As String
FileName = Dir(Path & "*.xlsx")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
    Workbooks.Open Path & FileName
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Next ws
    Workbooks(FileName).Close
    FileName = Dir()
Loop

Worksheets(1).Delete

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

Click Here for Free Excel Templates

Enrol in Simple Sheet's Excel University - FREE Taster Course

All purchases help to support this blog - thanks!

Creating a single workbook from a number of workbooks, using VBA requires a number of steps to be followed.

• You need to select the workbooks from which you want the source data – the Source files.
• You need to select or create the workbook to which you wish to put the data – the Destination file.
• You need to select the sheets from the Source files that you require.
• You need to tell the code where to place the data in the Destination file.

Combining all Sheets from all Open Workbooks to a New Workbook as Individual Sheets

In the code below, the files you need to copy the information from need to be open as Excel will loop through the open files and copy the information into a new workbook.  The code is placed in the Personal Macro Workbook.

These files are the ONLY Excel Files that should be open.

Sub CombineMultipleFiles()
On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
'turn off the screen updating to speed things up
   Application.ScreenUpdating = False
'first create new destination workbook
   Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
   strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
            sh.Copy After:=Workbooks(strDestName).Sheets(1)
         Next sh
      End If
   Next wb
'now close all the open files except the new file and the Personal macro workbook.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb

'remove sheet one from the destination workbook
   Application.DisplayAlerts = False
   Sheets("Sheet1").Delete
   Application.DisplayAlerts = True
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsSource = Nothing
   Set wb = Nothing
'turn on the screen updating when complete
   Application.ScreenUpdating = False
Exit Sub
eh:
   MsgBox Err.Description
End Sub

Click on the Macro dialog box to run the procedure from your Excel screen.

Your combined file will now be displayed.

This code has looped through each file, and copied the sheet to a new file. If any of your files have more than one sheet – it will copy those as well – including the sheets with nothing on them!

Combining all Sheets from all Open Workbooks to a Single Worksheet in a New Workbook

The procedure below combines the information from all the sheets in all open workbooks into a single worksheet in a new workbook that is created.

The information from each sheet is pasted into the destination sheet at the last occupied row on the worksheet.

Sub CombineMultipleSheets()
On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsDestination As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
   Dim iRws As Integer
   Dim iCols As Integer
   Dim totRws As Integer
   Dim strEndRng As String
   Dim rngSource As Range
'turn off the screen updating to speed things up
   Application.ScreenUpdating = False
'first create new destination workbook
   Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
   strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
'get the number of rows and columns in the sheet
            sh.Activate
            ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
            iRws = ActiveCell.Row
            iCols = ActiveCell.Column
'set the range of the last cell in the sheet
            strEndRng = sh.Cells(iRws, iCols).Address
'set the source range to copy
            Set rngSource = sh.Range("A1:" & strEndRng)
'find the last row in the destination sheet
           wbDestination.Activate
           Set wsDestination = ActiveSheet
           wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
           totRws = ActiveCell.Row
'check if there are enough rows to paste the data
           If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
               MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
               GoTo eh
           End If
'add a row to paste on the next row down
           If totRws <> 1 Then totRws = totRws + 1
           rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
      Next sh
   End If
   Next wb
'now close all the open files except the one you want
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsDestination = Nothing
   Set rngSource = Nothing
   Set wb = Nothing
'turn on the screen updating when complete
   Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

Combining all Sheets from all Open Workbooks to a Single Worksheet in an Active Workbook

If you want to bring the information from all other open Workbooks in to the one you are currently working in, you can use this code below.

Is there a way to combine multiple Excel files into one?

How to copy data from multiple workbooks into one using VBA?

How do I merge data in Excel VBA?