Pulling data to a master worksheet using VBA

[MrCrimmy]

Hi all! Still having trouble with this

I wonder could someone help me, I am trying to produce an activity analysis database for my team. We track our activity in our own spreadsheets, in total there is 16 of them. Each process has a subset of different activites, an example of which can be seen below: file path P:\LabGroup\ActivityTracker\Activity Analysis - Bryan.xlsx.
[TABLE="class: cms_table_grid"]
<TBODY>[TR]
[TD]Process[/TD]
[TD]Activity[/TD]
[TD]Time(hours)[/TD]
[/TR]
[TR]
[TD]BAU[/TD]
[TD]Meeting[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Development[/TD]
[TD]Research[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BAU[/TD]
[TD]Safety[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Expansion[/TD]
[TD]Training[/TD]
[TD]2[/TD]
[/TR]
</TBODY>[/TABLE]





This is done for each week so each workbook has about 6 sheets one for each week. This is then collected in a cumulative sheet as follows:

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Process[/TD]
[TD]Activity[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]BAU[/TD]
[TD]Meetings[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Safety[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Development[/TD]
[TD]Research[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Experiments[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Expansion[/TD]
[TD]Training[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Purchasing[/TD]
[TD]0[/TD]
[/TR]
</TBODY>[/TABLE]


This data is gathered using SUMIFS functions.

What I need to do is to collect the gathered data into a master cumulative sheet which will have table like the one above for each person. (Path P:\LabGroup\ActivityTracker\Cumulative Activity Analysis.xlsx.


Is there anyway I could use <ACRONYM title="visual basic for applications">VBA</ACRONYM> or some sort of macro to open each file copy out their cumulative data and insert it into the master sheet for review by our team leader?

Any help anyone could offer any assistance I would greatly appreciate it.

Kind Regards

Bryan​