Saving employee holidays from a monthly calendar

[johnnyking997]

Hello!
I hoped it would not be necessary to make a separate thread about my "problem", but I can't find an answer.
I am currently working on a monthly shift calendar that keeps track of how many hours an employee has completed/month, the holiday dates each one reserved for the past, current and next year.

If a specific date is empty(no holiday tag like "CO","CS", etc.), the cell contains the following formula that checks and displays the number of hours that an employee must do on that specific date:
Ex. for E12:
=IF($B$12 <> "", IF(E8<>"",IF(AND(MOD(E8,4)=0,E8>0),12,IF(AND(MOD(E8,4)=1,E8>0),5,IF(AND(MOD(E8,4)=2,E8>0),7,""))),""), "")

My question to you, how can I keep track of the each employee holidays?

My first ideea was to save the range E12:AI26 to another sheet. I used:

Worksheets("Calendar").Range("E12:AI26").Copy
Worksheets("Current_year_holidays").Range("some range").PasteSpecial Paste:=xlPasteAll

but in the "Current_year_holidays" sheet, I encountered a lot of "#REF!" when pasting:
=IF($B$12<> "", IF(#REF!<>"",IF(AND(MOD(#REF!,4)=0,#REF!>0),12,IF(AND(MOD(#REF!,4)=1,#REF!>0),5,IF(AND(MOD(#REF!,4)=2,#REF!>0),7,""))),""), "")
I am forced to use normal references for ease of use when removing holidays from the calendar by using autofill(it was my first idea on how to "delete" holidays and I haven't found an alternative).

Any tip is appreciated!

Regards,
Alex

 

[mabbutt]

Just sum the days that have a holiday code for each employee for each month:

=SUM(COUNTIFS($E$12:$AI$12, {"CO","CRP","SS"}))

Then add the total count for the month to the previous month for the annual total.

 

[johnnyking997]

Just sum the days that have a holiday code for each employee for each month:

=SUM(COUNTIFS($E$12:$AI$12, {"CO","CRP","SS"}))

Then add the total count for the month to the previous month for the annual total.

Thank you for your response!
It's not a matter of saving the amount of days for each employee, but to save the dates of said holiday.
Ex.:
For employee 1, in January, 1 "CO" type holiday, between 8th and 14th.
The idea behind this is to create a dynamic calendar. Once the calendar month is changed, it should retrieve the holidays for that specific month. The workflow behind it, in my opinion, should be:
Ex.:
Employee 1-> Add holiday in January between date a and b-> Change month to february-> Add holiday between c and d-> Switch back to January-> Update the table with saved holiday

I'm sorry if the original post wasn't descriptive enough about what I am trying to accomplish.

Thanks for your attention!

 

[mabbutt]

So you want the columns AK:AO on each monthly sheet to reflect any holiday added on any of the other monthly sheet?

For example, in the sheet above on row 12 the employee has 5 holidays for code CO and this is represented in AN12.

If in the February sheet you add an additional holiday for that employee of say 2 days for code CO, you want the value on both sheets to reflect each other e.g. a total of 7 holidays.

Is that correct?

 

[johnnyking997]

I can't find a way to properly explain what I'm trying to achieve, so here is a video that sort of explains it:
<< unavailable video removed >>
Thanks!