Hi, I’m wondering if anybody out there has the skills to work this out – because I certainly don’t! So any help would be most appreciated!
I have two workbooks: 1. Master Timesheet Summary and 2. Resource Capacity
The Master Timesheet Summary is a consolidated list of all of the times people have worked on different projects for each week of the year. It is a simple list laid out as follows:
ColA: Name; ColB: Date; ColC; Project; ColD: Hrs; ColE: Days
The list gets longer each week and is currently 930 rows long.
The Resource Capacity spreadsheet has a list of each person’s name and the projects they’re working on, and the hours spent on each project with totals. It’s uses all sorts of formula/functions to make it work such as VLookup etc.
ColA: vlookup reference; ColB: Name; ColC: Project; ColD: Profile; ColE-ColIV:Months.
On Row 6 in each column starting at ColE there are weekend dates (eg 2 Dec 2007, 9 Dec 2007 etc)
What I need to do is run a macro (I assume) that copies the timesheet data from the Master Timesheet Summary and pastes it into the Resource Capacity sheet. So, If Fred is currently allocated to Project A, Project X and Project Z, the macro checks the name, project and the current date range in the Resource capacity sheet and then looks for matching data in the Master Timesheet Summary and then grabs the corresponding time from Col E (Days) and pastes it into the Resource Capacity sheet then moves onto the next person.
I’m not sure if this makes sense, and how easy it is, but would appreciate any help anybody can provide.
Thanks very much.
Dave.
I have two workbooks: 1. Master Timesheet Summary and 2. Resource Capacity
The Master Timesheet Summary is a consolidated list of all of the times people have worked on different projects for each week of the year. It is a simple list laid out as follows:
ColA: Name; ColB: Date; ColC; Project; ColD: Hrs; ColE: Days
The list gets longer each week and is currently 930 rows long.
The Resource Capacity spreadsheet has a list of each person’s name and the projects they’re working on, and the hours spent on each project with totals. It’s uses all sorts of formula/functions to make it work such as VLookup etc.
ColA: vlookup reference; ColB: Name; ColC: Project; ColD: Profile; ColE-ColIV:Months.
On Row 6 in each column starting at ColE there are weekend dates (eg 2 Dec 2007, 9 Dec 2007 etc)
What I need to do is run a macro (I assume) that copies the timesheet data from the Master Timesheet Summary and pastes it into the Resource Capacity sheet. So, If Fred is currently allocated to Project A, Project X and Project Z, the macro checks the name, project and the current date range in the Resource capacity sheet and then looks for matching data in the Master Timesheet Summary and then grabs the corresponding time from Col E (Days) and pastes it into the Resource Capacity sheet then moves onto the next person.
I’m not sure if this makes sense, and how easy it is, but would appreciate any help anybody can provide.
Thanks very much.
Dave.
