Excel Macro to Combine Multiple Files and List Source Files in New Column
In this video I'm going to show you to use a macro to automatically combine multiple Excel Files.
It's available at the Microsoft Office Dev Center here: https://msdn.microsoft.com/en-us/libr... OR on my site at http://www.litigationsupporttipofthen...
Here we have the macro already inserted in Visual Basic. In Excel you open Visual Basic by pressing ALT + F11. Then right click on the Modules folder and select Insert . . . Module, and just paste the VBA code in.
In this demo, we're going to use three Excel files. The files have the same kind of data in an equal number of rows.
Each has data in columns A to K and none has more than 25 rows.
Several lines down into the macro we need to designate the path to the folder which contains the Excel files we want to combine.
See the line which begins:
My Path =
Next we scroll down to the line beginning:
Set sourceRange =
. . . and enter the cell range in which data is to be collected from in all of the source spreadsheets. In this demo the range will be A1:K25.
As we can see the data from all three spreadsheets is combined on a new spreadsheet.
The macro also creates a new field in column A of the merged spreadsheet containing the file name of the source file.