Hello all,

I am trying to break down a large sheet of data [jobs for a construction
company] down to a report per estimator in the list. I have a way of doing
it, but it seems a little bulky. Here is how I am doing it now:

Sub collect_est_data()

Dim est1(1 To 33), est2(1 To 33), est3(1 To 33), est4(1 To 33), est5(1 To
33), est6(1 To 33), est7(1 To 33), est8(1 To 33), est9(1 To 33), est10(1 To
33), est11(1 To 33), est12(1 To 33), est13(1 To 33), est14(1 To 33) As Range

For Each i In Worksheets("Bid Card").Range("P3:P1000")
For j = 18 To 46 Step 4
If Worksheets("Bid Card").Cells(i.Row, j).Value < "" Then
Select Case Worksheets("Bid Card").Cells(i.Row, j).Value
Case "Estimator name1"
Select Case j
Case 18 ' Mechanical
est1(1) = est1(1) + 1
est1(2) = est1(2) + 1
est1(3) = est1(3) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(4) = est1(4) + 1
est1(5) = est1(5) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 22 ' Electrical
est1(1) = est1(1) + 1
est1(6) = est1(6) + 1
est1(7) = est1(7) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(8) = est1(8) + 1
est1(9) = est1(9) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 26 ' Piping
est1(1) = est1(1) + 1
est1(10) = est1(10) + 1
est1(11) = est1(11) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(12) = est1(12) + 1
est1(13) = est1(13) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 30 ' Fab
est1(1) = est1(1) + 1
est1(14) = est1(14) + 1
est1(15) = est1(15) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(16) = est1(16) + 1
est1(17) = est1(17) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 34 ' Rental
est1(1) = est1(1) + 1
est1(18) = est1(18) + 1
est1(19) = est1(19) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(20) = est1(20) + 1
est1(21) = est1(21) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 38 ' Sub
est1(1) = est1(1) + 1
est1(22) = est1(22) + 1
est1(23) = est1(23) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(24) = est1(24) + 1
est1(25) = est1(25) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 42 ' Engineering
est1(1) = est1(1) + 1
est1(26) = est1(26) + 1
est1(27) = est1(27) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(28) = est1(28) + 1
est1(29) = est1(29) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 46 ' Civil
est1(1) = est1(1) + 1
est1(30) = est1(30) + 1
est1(31) = est1(31) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) < 0 Then
est1(32) = est1(32) + 1
est1(33) = est1(33) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
End Select
Case "Estimator Name2"
[Inserts values into the array]
End Select
End Select
End If
Next j
Next

End Sub

And so on and so forth until all the estimators have been completed. This
just seems a little large in code, and I was thinking of writing a function,
but for some reason couldn't get it to dynamically pass the array I needed
to dump to. After all of this is collected, it will get put into a sheet in
the workbook, which is a block of data for each person.

Is my thought process even right here?

Thanks for any help,
-=Alejandro