Find alternative to using nested loops for subtotalling data

[totalchaos]

I currently have data (below) that I sort by date, then job, then trade and then shift. For each date, job, trade and shift, I must provide subtotals and output the results to a separate sheet.

The code I currently use has nested do..loops which navigates the data, fills an array and writes to a separate sheet. The code (below) is the gist of what I have. The actual code is so complex to look at it gives me a headache and is hard to manage.

I'm looking for an alternative to the approach I've taken. I researched using autofilter. Unfortunately, from I can tell, I cannot get the list of unique values inside each filter which I would use to store in an array and then loop through and autofilter the specific values in the 4 fields I listed in the first paragraph. I'd then use the worksheetfunction.subtotal to get my subtotals. If anybody knows how to do this, it would be greatly helpful to me to simplify my code and possibly provide some performance efficiency. If it's not possible to use autofilter, any other suggestions would be greatly appreciated to simplify my code and improve the efficiency since my actual list is much larger. I'm using excel 2007 and 2010 on different computers.

Here is the data:

[TABLE="width: 480"]
<colgroup><col style="width: 48pt;" span="10" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Excel 2007[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"] [/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]B[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]C[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]D[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]E[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]F[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]G[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]H[/TD]
[TD="class: xl66, width: 64, bgcolor: #E0E0F0"]I[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Line#[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Employee[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Job[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Trade[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Code[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Extra #[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Shift[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Work Date[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Hours[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]5[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]7[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]8[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]9[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]11[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]12[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]13[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]14[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]15[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Electrician[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]16[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]17[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]18[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/19/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]19[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]20[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]21[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]22[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Plumber[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]23[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]24[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]25[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1202[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #E0E0F0"]26[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1201[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Carpenter[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]7/18/2012[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Sheet1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Here is the code I currently use in a standard module:

Option Explicit
Dim wsSrce As Worksheet, rng As Range, sngHours As Single, strWrkDate As String, _
strJobNum As String, strTrade As String, strShift As String, Cntr As Long, _
arr() As Variant, i As Integer

Private Sub WorkReport()
Set wsSrce = ActiveWorkbook.Worksheets("Sheet1")
Set rng = wsSrce.Range("A1:I26")
'Sort by workdate, job, trade, shift
With wsSrce.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("H2:H26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C2:C26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D2:D26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("G2:G26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'sum hours for each unique workdate, job, trade, shift combination
With wsSrce
Cntr = 1
Do Until IsEmpty(.Cells(Cntr, 1)) '1
Cntr = Cntr + 1
strWrkDate = .Cells(Cntr, 8)
Do While .Cells(Cntr, 8) = strWrkDate '2
strJobNum = .Cells(Cntr, 3)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum '3
strTrade = .Cells(Cntr, 4)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum And .Cells(Cntr, 4) = strTrade '4
strShift = .Cells(Cntr, 7)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum And .Cells(Cntr, 4) = strTrade And .Cells(Cntr, 7) = strShift '5
sngHours = sngHours + .Cells(Cntr, 9)
Cntr = Cntr + 1
Loop
FillArray
sngHours = 0
Loop
Loop
Loop
Loop
End With
WriteArray
'sort back to original order
With wsSrce.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A2:A26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub FillArray()
i = i + 1
ReDim Preserve arr(1 To 5, 1 To i)
arr(1, i) = strWrkDate
arr(2, i) = strJobNum
arr(3, i) = strTrade
arr(4, i) = strShift
arr(5, i) = sngHours
End Sub
Private Sub WriteArray()
Dim a As Integer, wsTrgt As Worksheet, lngLR As Long
Set wsTrgt = ActiveWorkbook.Worksheets("Sheet2")
For a = 1 To UBound(arr, 2)
With wsTrgt
lngLR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lngLR, 1) = arr(1, a)
.Cells(lngLR, 2) = arr(2, a)
.Cells(lngLR, 3) = arr(3, a)
.Cells(lngLR, 4) = arr(4, a)
.Cells(lngLR, 5) = arr(5, a)
End With
Next a
Erase arr
End Sub

Any help is greatly appreciated!

 

[memar_one]

By the way, try:

=SUMPRODUCT(--(Sheet1!$H$2:$H$1000=$A3), --(Sheet1!$C$2:$C$1000=$B3), --(Sheet1!$D$2:$D$1000=$C3), --(Sheet1!$G$2:$G$1000=VALUE(RIGHT(D$2, 1))), Sheet1!$I$2:$I$1000)

Put this in sheet 2 in cell D3 and copy through cells D3:G12.

I tried to follow the format of your above example result, and this will add up hours by date and job and trade and shift, but not by code.