Return to VBA Code Examples

VBA Combine Multiple Excel Files into One Workbook

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.

vba merge books

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

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

vba multiple files combine run macro

Your combined file will now be displayed.

vba multiple file combined

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.

 

VBA 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! vba save as


Learn More!