I am new to macros and can get by with basic programming, but struggle with
loops etc.

I currently receive summary date of jobs (circa 100 per month) and their
estimated Work in Progress values on a monthly basis. The estimated %
completion on each job is an estimate based on the relevant individuals "Gut
Feel" based on how the job is progressing, rather than a more "mathematical"
approach. I have access to the detail which makes up the summary information
and would like to apply a macro to test the "reasonability" of the summary
figures I am receiving.

The summary info is in the following format:

Job No Job value Sales Value of work to date %
Complete Division WIP
10256 50,000 15,000
40% 1 20,000
These are not broken down by Department
10257 100,000 15,000
10% 2 10,000

The Value of Work to date is Sales price x Time spent and would not take
into account efficiencies / inefficinecies in production, so job 10256 is
40% complete despite the value of work to date only amounting to 30% of the
job value.


The detailed info would be as follows (circa 20,000 lines per month):

Job No Description Division Department
Quantity Unit Sale Price Total Sale Price Unit
Standard Cost Total Std Cost
10256 Machine 1 1 1
5 500 2500
260 1300
10256 Machine 2 1 2
3 200 600
160 480
10256 Machine 1 1 1
1 500 500
260 260
10256 Machine 3 1 3
4 300 1200
200 800
10256 etc


What I think I need to do is the following:

1. Name the range in which the summary info is held.
2. Get the first job no. in the summary info
Filter the detailed information by this job number and then
subtotal the Total Sales Price column
Lookup the Value of work to date on the summary sheet and apply
the value to a cell.
Divide the Detailed Total Sales Price by the lookup value.
Apply the % achieved by this formula to all the cells in the in
the Filtered Detail page (This would then give me a departmental WIP as the
% would be applied to each detailed
line)
Loop to the next job in the summary information

I am currently doing this manually and it is extremely slow.

Any help would be appreciated. Please let me know if you need any of this
needs further clarification. I am interested in the macro to achieve the
above. The logic of what is being done is difficult to explain as there are
further levels of detail which would need analysis, but sorting the macro
out would clear a lot of them up, as well as providing me with the model
for further analysis.

Thanks in advance.

Craig