Using VBA to collate data to master spreadsheet

[MrCrimmy]

Hi all!

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: grid, width: 500"]
<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="width: 500"]
<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 VBA 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

 

[SRIKANTHBENONI]

Hi,

I have couple of Queries?

1 - Are individual sheets are saved in same path where Master file saved?
2- When you paste the data into master data, do you want to paste it below the existing data with Emp Name as below?

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Process[/TD]
[TD]Activity[/TD]
[TD]Time Emp Name[/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]

 

[MrCrimmy]

Hi,

I have couple of Queries?

1 - Are individual sheets are saved in same path where Master file saved?
2- When you paste the data into master data, do you want to paste it below the existing data with Emp Name as below?

[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[TD]Process
[/TD]
[TD]Activity
[/TD]
[TD]Time Emp Name
[/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][/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]

Hi there! Thanks for your speedy reply

1.Yeah all the individual workbook are in the same folder as the master workbook.

2. What I had intended to do was have one sheet in the master workbook that would have individual lists for each person so there would be one sheet with 13 tables where I could see what each person was doing and then a second sheet which adds them all together and plots them, so a data collection sheet and then a sheet which would have all the activity for the department using SUMS or something simple enough.

I hope that was clear!

Kind Regards,

Bryan