Converting cell formulas to VBA Code

Hi,

I receive a spreadsheet from another department that shows Start and Finish dates for activities. I paste this sheet into my spreadsheet in a sheet called ‘PI & Data”. I need to lay out associated data such as Quantities and Manhours over a timeline (similar to a Gantt Chart). (And no, I can’t just use MS Project. It has to be Excel).
When this started I was getting 49 rows of data so I just set up a worksheet for each combination of Area, Discipline and Cost Code and had a timeline across the top row ( a cell for each day with the date as the heading). In each cell I placed a formula that extracted the Quantity or Manhour (per day) from the source data by Area, Discipline and Cost Code. There were 49 rows that aligned with the 49 rows in the source data. The daily values were then rolled up into weekly values by summing along the bottom of the range. These weekly values are then referenced on another sheet called 110Figures.
So there was a sheet for Area 110A, Discipline ME and Cost Code 15.3xxx, for example. There are around a dozen of these sheets. The formula in each cell is:

=IF(AND('PI & Data'!$C2="ME", D$3>='PI & Data'!$F2, D$3<='PI & Data'!$G2,'PI & Data'!$H2=19.913,(LEFT('PI & Data'!$B2,4)="110A")), 'PI & Data'!$K2, " ")

If I had 4 instances where the combination 110A, ME and 15.3xxx occurred I would have 4 rows populated in that sheet and the remainder empty.
This may have been clumsy but was the only way I knew to do this and it worked great. Problem is the source data now runs to 400 or more rows! It is obviously not practical for me to have 400 rows of formulas in each of my sheets.
Can I do the same thing, using VBA I guess, where I only get 4 rows returned if there are 4 matches in the source data and I don’t have all these cells full of formulas? I still need to roll the daily values up to weekly and use those weekly values on other sheets.
What would the code be?
I will attempt to attach a sample file (with most sheets deleted to reduce file size).

Thank you very much

Dave