Using VBA to collate data to master spreadsheet

MrCrimmy

New Member
Joined
Sep 26, 2014
Messages
14
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.
ProcessActivityTime(hours)
BAUMeeting1
DevelopmentResearch5
BAUSafety1
ExpansionTraining2

<TBODY>
</TBODY>




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:

ProcessActivityTime
BAUMeetings1
Safety1
DevelopmentResearch5
Experiments0
ExpansionTraining2
Purchasing0

<TBODY>
</TBODY>

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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

SRIKANTHBENONI

New Member
Joined
Jun 30, 2010
Messages
46
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?

ProcessActivityTime Emp Name
BAUMeetings1
Safety1
DevelopmentResearch5
Experiments0
ExpansionTraining2
Purchasing0


<tbody>
</tbody>
 

MrCrimmy

New Member
Joined
Sep 26, 2014
Messages
14
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?

Process
Activity
Time Emp Name
BAU
Meetings
1
Safety
1
Development
Research
5
Experiments
Expansion
Training
2
Purchasing
0


<TBODY>
</TBODY>

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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,442
Messages
5,547,945
Members
410,820
Latest member
Prepost
Top