Macros with SUMIFS that changes dependent on month

CAP - Tool for posting.xlsmI need help with both formulas/function AND a macro.
I have attached a workbook. I am trying to use a macro to fill in the red boxes (I have just made the cells red to make this easier to understand, the macro doesn’t need to do this). The report attached contains several months of data. In this case, the report starts with January. I need formulae that add up the weekday and weekend data. The weekday total –current like (for example, row 146) should add up the figures in the same column that are weekdays (as indicated in column “A”) that are on the current line for the various dates. The next row should add up the “Last Yr,” line and the weekend totals section should do the same for weekends.

The reason why I think I need a macro to plug in these formulas is the report could change every time it is ran, so I think a macro that searched for the right area of the spreadsheet to add would be brilliant. I’m open to other ideas if there are any. I really appreciate any help you can give.
Thanks!

Hi Wadelair,

Here's a macro for the 24 fields in the red boxes - let me know of any issues:

Please Login or Register  to view this content.

Hi Wadelair - glad you like it - here is a commented version:

Please Login or Register  to view this content.

Hi Wadelair,

I upped all the numbers but had to code a bypass errors - see if it's what you wanted:

Please Login or Register  to view this content.

Hi Wadelair,

This will post only in the red cells (They have to be red). What Formulae are you considering?

Please Login or Register  to view this content.

The n index wasn't being incremented, for some reason (or no reason at all) the routine was skipping over that code:

Here's the fix:

Please Login or Register  to view this content.

Are SetCapacity-HotelSupply calculated numbers or are they on the sheet under another guise??

Please Login or Register  to view this content.

I get a reference error on Hotel Supply!

You know - since we're overwriting the "green" fields there is no reason to restrict the first writing to only "red" fields:

Please Login or Register  to view this content.