Monday, September 23, 2013

How to Combine Excel Files

There have been countless times during my day job when I’ve had to combine multiple Excel files into one. I used to do it the old fashioned way of copy and paste but after doing this tedious and time consuming task I decided to write a VBA macro to help me out. I’ve now made the template available for you to use for free. This version of the combine Excel workbooks macro allows you to choose the directory of the Excel files to copy without having to change the code in the Visual Basic Editor. The steps to use the Combine Workbooks tool are as follows:

combine excel files macro
 
  1. Place the Excel workbooks you want to combine into a single folder that contains no other files
  2. Go to View>Macros
  3. Select "Combine Workbooks" then click Run
  4. Select the File where the spreadsheets you want to combine are located
  5. Save the newly created file

Instructions are provided with the spreadsheet

Please note that this code will not work on protected sheets and it will automatically skip any blank worksheets in any of the workbooks saved in the folder. See how it works for yourself by watching my short demo video below:
 

 
Download the Combine Excel Files spreadsheet that contains the VBA code to copy all the worksheets from all the workbooks in a folder into the active workbook using this link:
 

Combine Files.xlsm download