I am working on my company project to calculate average of daily production efficiency maintain in excel template. Each day create new file with the day and enters the data and calculate the efficiency using same template.
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. Each sheet has the data of individual employees efficiency in same range ( B20 : B50). As I said earlier we have more than 300 files in same template with data in it. I tried to write formula using Average (indirect( " range and file name")) but works only when I open the workbooks.
Could anyone help me to write VBA code to achieve the task.
Thank you
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. Each sheet has the data of individual employees efficiency in same range ( B20 : B50). As I said earlier we have more than 300 files in same template with data in it. I tried to write formula using Average (indirect( " range and file name")) but works only when I open the workbooks.
Could anyone help me to write VBA code to achieve the task.
Thank you
