I am assuming all the reports are xls files (Excel 2003) and you are using Excel 2003. The workbook I have attached has a button that when pressed will allow you to navigate to the directory containing the report files you want to combine into one report. All the files you want to combine into one report should be in a single folder. Once you select the folder with all the report files the code will copy all the data from the report files (assuming the data as in your example is in Sheet 1) and place it into a new workbook. The workbook is created in the subdirectory called Output. If the Output directory does not exist then it will be created. The workbook I create that is a combination of all the report files is named CombinedReports and I append the current date to the filename so it is a unique file. If the macro is run a second time I create a new file called CombinedReports and append the current date as well as the current time so that way the files do not get written over. Once the combined report is generated a message box will pop up advising you if the process has been successful and then it will open the combined report workbook for you.
Test this on some test files first and make sure you have backups. The code will not affect the report files/overwrite them or change them however I recommend doing backups just in case until you are confident the code does what you require. Every day you want to run the process of combining the reports just open the attached workbook and press the button to execute the code. This workbook can be placed in any directory however I would recommend NOT placing it into the directory with the report files - place it into it's own Folder. As I say as you have to do this daily then I think naming the file with the current date will allow you to identify the combined reports easily. The code is as follows.
If you use Excel 2007 then the code would need to be modified slightly so if you use Excel 2007 let me know. Only a few lines would need changing.
PS: There is a small amount of code in Worksheet 1 that starts the code in the module
Bookmarks