A VBA macro to merge data from multiple Excel files into a single file

This is an example of a real project requirement. It does not go into detail due to customer confidentiality issues and the purpose is to show how a macro can solve the problem not to demonstrate the coding techniques.

Data had been collected in single Excel files with one record per file. Each record was a set of name and value pairs, stored in the first sheet of each workbook. The required result was a single workbook with one record per row.

If there were only a handful of input files it would be reasonably simple to open each file in turn and copy the column to the clipboard and then paste to the master workbook using the transpose option. This becomes less feasible as the number of input files increases. If there are hundreds or even thousands of input files, it becomes a major piece of data entry work.

The solution was to:

1. Place all the input workbooks into a separate folder, containing no other files. The master workbook containing the combined data was to be stored at another location.
2. Create an empty workbook containing the macro that will run through the following process.
3. Open the master workbook and make a note of the next empty row.
4. Loop through all the input files in the folder.
5. Read each value in turn and copy it to the current row in the master file.
6. Save and close the master workbook.

The job is done and the macro can be reused again and again if more data files are created in the future.