Copy Newest Data From Multiple Locations with Multiple Workbooks to a Master Workbook

Hi Everyone,

I've been looking high and low to see if I could find something that works for what I need to do and so far no luck.

I hope this makes sense. If it doesn't please let me know and I'll do my best to clarify.

I need to copy multiple raw data workbooks (with only one worksheet each) into multiple worksheets to a single master workbook.
For example:
o File1.xls copied into MasterWorkbook.xls Sheet 1
o File2.xls copied into MasterWorkbook.xls Sheet 2

Raw data workbooks are saved in multiple directories with multiple .xls files with a common naming pattern based on the date. The newest workbook from each directory is the one that needs to be copied into the MasterWorkbook.xls to the sheet I designate.
For example:
o 01-01-2016.xls and 01-02-2016.xls are in the same folder. We want the newest data to be copied into Sheet 1. 01-02-2016.xls is the newest so this is the file that needs to be copied to MasterWorkbook.xls Sheet 1. Say I put another .xls file in that same directory named as 01-03-2016.xls. Since this workbook has an even newer date, I need this to be copied into the MasterWorkbook.xls Sheet 1 so that it replaces the older data from 01-02-2016.xls.
All of the raw data workbooks contain the same format. I need to be able to copy the newest workbooks from other directories into different sheets in the MasterWorkbook.xls as well.

Thanks in advance.

Hi there,

Can the worksheets in the Master workbook be named with the appropriate date (e.g. "01-02-2016") rather than "Sheet1", "Sheet2" etc.? This approach would make it easier to check whether or not a raw data file had already been copied to the Master workbook.

Regards,

Greg M

Originally Posted by Greg M

Hi there,

Can the worksheets in the Master workbook be named with the appropriate date (e.g. "01-02-2016") rather than "Sheet1", "Sheet2" etc.? This approach would make it easier to check whether or not a raw data file had already been copied to the Master workbook.

Regards,

Greg M

Hi Greg,

The sheets could be named differently but because I will have another sheet with formulas that takes the data from the raw sheets, the tab names would have to remain static in the Master Workbook. I need the newer data to overwrite what's currently in the worksheets.

Thanks.