VBA Code to amalgamate data from separate workbooks into a master workbook

**scousemouse** · 11-21-2012, 07:55 AM

Hi, I am a newbe vb programmer, who has been asked at work to come up with a spreadsheet to log individuals work. My aim is to cut down on double handling the work required. So I have come up with the idea of having separate files for each person which will allow them to log tasks against time taken. Then in a master file almalgamate all the results. From this I will be using the data to create pie charts.

I have attached a sample log file called Ashley Cat Log sheet.xls, which the individual uses to log their times. There is also a tab called tasks which I am using to create a dropdown list for data validation purposes. The master sheet is called Cataloguing Times.

Hope somebody can help

**arlu1201** · 11-21-2012, 08:23 AM

Couple of questions -

1. Are all the individual files stored in one folder?
2. Once you run the macro for the day, and then the next day more data is input into the individual files, it will be added to the next available row in the individual files, right? But the macro will start copying from row2 (after the header), so that will create duplicate data. Do you want the macro to clear the master file and populate data from the individual files?

**scousemouse** · 11-21-2012, 12:51 PM

Hi, Thanks for replying. Firstly I am going suggest keeping the files in a separate folder, as it will help admin and coding. The code will probably be run at the end of each week/month but not daily. Regarding the new data it would need to be appended to the previous month. But first I'll ask my manager so that we are clear.

**scousemouse** · 12-15-2012, 02:47 AM

OK, What I can gather from the spec is that there will be individual files for each person, the data from each needs to be copied into the master file, which has a tab for each person. Then much copied sub totals are required for how long each task took to complete, also a grand total of all the time taken. They want to use this data to create monthly totals from the raw data, so the data would be updated once a month, any new data would need to appended to any existing data for that individual. Thanks for any help

**TMS** · 12-15-2012, 05:33 AM

Just a point for consideration. If you consolidate all your data for each month for every staff member into a single sheet, rather than separate staff sheets, you will be able to take advantage of standard Excel facilities such as Sort and Filter for listing and presentation and Pivot Tables and Charts for analysis.

As you are at the early stages of the design, it might be better to consider this now rather than later.

Your follow up question(s), if you continue with the current design, is likely to be: "I have all these sheets for staff members, how do I analyse the time, productivity, compare their performance, ..."

Regards, TMS

**scousemouse** · 12-16-2012, 05:25 AM

That is a good point as the manager will still have access to the original data from each person, if they require it. At present each individual is writing down on scrap paper their times, so I definitively want to get away from that. So lets go for a single master sheet which also has a column that lists the persons name, is it possible to get extract their name from the file name?