Automatically send specific data from multiple worksheets to one workbook.

[tentoria]

Hi everyone,

I will explain my scenario and I am hoping for some help please.

I work in a training department where trainers assess trainees on an excel file. Each trainer generates a new workbook where they enter the details of the trainees and on the workbook an individual sheet is created for each trainee. The trainer creates a new folder on the SharePoint specific for their group where thee workbook they generated is saved. Once the course is finished, the trainer will manually enter the data from the practical assessments on MS Forms. I want to automate this process where the the data from different sheets in different workbooks is automatically sent to one main workbook. I can then generate a Power BI dashboard from that main workbook.

To make it more clear, we have up 8 groups every week. Each trainer for each group generates a different workbook with multiple sheets and saves it in their respective folder on SharePoint.

I am attaching an example of the workbook where you will be able to see the individual trainees sheets and one sheet called 'Data Collection'.

[FDibbins]

Formulas cannot "send" anything, anywhere, they only operate in the cell they reside it. Then can, however, "get" info from other places, so your "summary" file can get into from the trainer's file for you.

[tentoria]

Hi Ford,

Thank you for your reply.

I am not an expert in Excel/VBA/Coding. Would you be able to simplify it for me and assist with the code please?

Thank you.

[FDibbins]

Coding is not my strong side, I have asked for some assistance for you