Return to VBA Code Examples In this Article
This tutorial will show you how to combine multiple Excel files into one workbook in VBA.
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 SubClick 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.
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.
Sub CombineMultipleSheetsToExisting() 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 rngEnd As String Dim rngSource As Range 'set the active workbook object for the destination book Set wbDestination = ActiveWorkbook 'get the name of the active file strDestName = wbDestination.Name 'turn off the screen updating to speed things up Application.ScreenUpdating = False 'first create new destination worksheet in your Active workbook Application.DisplayAlerts = False 'resume next error in case sheet doesn't exist On Error Resume Next ActiveWorkbook.Sheets("Consolidation").Delete 'reset error trap to go to the error trap at the end On Error GoTo eh Application.DisplayAlerts = True 'add a new sheet to the workbook With ActiveWorkbook Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count)) wsDestination.Name = "Consolidation" End With '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 in the sheet sh.Activate ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate iRws = ActiveCell.Row iCols = ActiveCell.Column rngEnd = sh.Cells(iRws, iCols).Address Set rngSource = sh.Range("A1:" & rngEnd) '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 you are not in row 1 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 SubVBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
How do I automatically merge Excel sheets into one?
On the Data tab, under Tools, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet, select your data, and then click Add.