Pulling data to a master worksheet using VBA

MrCrimmy

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,226,695
Messages
6,192,481
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top