Hi! I have developed 2 excel workbooks:
- A project budget tracker
- A sub-programme budget tracker (for which multiple project trackers will be accumulated in)
For example I have 3 different project trackers, and I need to sum the values up in the sub-programme tracker.
I have developed a macro to export the data from the project tracker to a file in C:\Temp. What I require is:
- Code to be developed in the sub-programme tracker to import the file from temp directory to a new tab in the sub-programme tracker
- Accumulate costs (resource costs & non-resource costs) into main tracker for all projects which have been imported into the sub-programme tracker
- Error handling for when the above does not work
Attached are 2 workbooks?
1. ? ? ? ? Project Budget Tracker_TEST (password is Budget)
2. ? ? ? ? Sub-Programme Budget Tracker_TEMPLATE
Project Budget Tracker_Test is for **individual** projects. These individual projects need to be viewed across a **sub-programme** (i.e. multiple projects in 1 sub-programme).
Requirements are as follows:
1. No formatting, cell values, code or anything else can be modified in the project tracker (Project Budget Tracker_Test). This is already in use by the user community and cannot be changed.
2. The 'export' macro I have developed will extract the required sheet to c:\temp, named as the 'Project ID'.xls - e.g. if project ID = R0800XX then the exported sheet will be called R0800XX.xls. This code cannot be changed.
3. The VBA Macro that I require should be implemented in the sub-programme tracker (Sub-Programme Budget [url removed, login to view]).
4. It should be applied to the button above the 'import project ID' text. This is where the user will enter the project ID (e.g. R080001) and click the button, after which the project sheet will be imported into the workbook.
5. There are 2 sections in the main tab: manpower costs and non-manpower costs: manpower costs should have the number of days imported to the main tab (INPUT-cost tracking-Current Yr) as well as the actual cost of that resource.
6. The non-manpower costs should also be imported to the main tab in the appropriate section.
7. It is very important that when multiple projects are imported into the sub-programme tracker, these costs are **accumulated/summed** for all projects imported (e.g. where a resource has the same Name for multiple projects, this resource has their cost/days worked summed up in the main tab of the sub-programme tracker). This also applies to non-manpower costs - e.g. if a non-manpower cost = 'Software Development' across 2 projects, then once both projects are imported there should be one line item for 'Software Development' with the accumulated value.
It is up to the discretion of the programmer how to develop the above. I do not mind if projects are imported below the data in the main tab (INPUT-cost tracking-Current Yr) or whether they are kept as separate tabs.
Please contact me if you have any questions - I will be happy to help.