I have requirement to copy a range of data from multiple workbooks into one workbook, but the ask is not to save the macro in either of the workbooks. So i need a third file which would have the names of the files listed in Cell A4 to A12 (can be more files as well). Cell A2 would consist the date when I select the date the names of the workbooks which are listed in cell A4 to A12 will get updated since they all would have date in their names, once the names are updated I would run the macro which would first open the main template workbook, the name of which would be in Cell B2 and it's path is in C1 and then open each file in loop from cell A4 to A12 and copy the data from the range which is mentioned in the cell C4 to C12 from each source file and paste it into the template file range mentioned in column D4 to D12, for each file the path would be different so the path will be mentioned in Column B .i.e. B4 to B12. Incase the current date file is not available on the path then column E4 to E12 would get populated with text "File not available", if available and the data is copied then "File Available. Data Copied"
Below is the sample structure of the file.
<tbody>
</tbody>
Below is the sample structure of the file.
Select Date: | Main Template File Name: | Template File Path: | ||
11/21/2017 | template.xlsx | c:\users\template\ | ||
Source File Names | Source File Paths | Source File Data Range | Template File Paste Range | File Status |
File A_21_11_2017.xlsx | c:\users\folder A\ | A1:A30 | C1:C30 | File Available. Data Copied |
File B_21_11_2017.xlsx | c:\users\folder B\ | A5:A20 | D5:D20 | File Available. Data Copied |
File C_21_11_2017.xlsx | c:\users\folder C\ | A2:A5 | C2:C5 | File Available. Data Copied |
File D_21_11_2017.xlsx | c:\users\folder D\ | E7:E20 | F7:F20 | File Not Available |
File E_21_11_2017.xlsx | c:\users\folder E\ | C4:C12 | I4:I12 | File Available. Data Copied |
File F_21_11_2017.xlsx | c:\users\folder F\ | B2:B17 | K2:K17 | File Available. Data Copied |
File G_21_11_2017.xlsx | c:\users\folder G\ | D1:D18 | F1:F18 | File Available. Data Copied |
File H_21_11_2017.xlsx | c:\users\folder H\ | F14:F15 | L14:L15 | File Not Available |
File I_21_11_2017.xlsx | c:\users\folder I\ | K12:K15 | J12:J15 | File Available. Data Copied |
<tbody>
</tbody>