Calculate Holidays Taken across whole Workbook

[Shazz]

Hi All,

I need to know how I can calculate all holidays taken across 52 tabs, attached is a example copy of what I need.

Each persons holiday is input on each tab and then I need the Summary tab to show a yearly total, once all tabs have been completed.

Any help would be greatly appreciated.

Shazz
x

 

[vletm]

Shazz ... hmm?
There are some challenges ... but it's You choice.
1) You could use =Get_Holidays_Taken(Row())-function from Module1
2) You could add Refresh_Holidays_Taken to all Your Worksheet_Change
I tested both and someway #2 works better ...
( I also 'cleaned' D & E-columns ... long formulas )
...
Question: Why do You have 52 times same kind of sheet?
... of course, one sheet needs
and then there would be less work for You ... an idea?

 

[Shazz]

vletm,

I need 52 separate sheets becasue there are 52 weeks in the year.

I've tried both of the ways above and it does not work for me in my spreadsheet, can I message you with the spreadsheet for you to take a look at?

Shazz

 

[Shazz]

Attached is the code you have used, by looking at one of my tabs on the attached Screen Shot, can you see what I am doing wrong?

Function Get_Holidays_Taken(y)
On Error Resume Next
Application.ScreenUpdating = False
With Sheets("Summary")
ww = 0
For w = 1 To 52
ww = ww + WorksheetFunction.CountIf(Sheets("wk " & w).Range("I" & y + 2 & ":O" & y + 2), "H")
Next w
Get_Holidays_Taken = ww
End With
Application.ScreenUpdating = True
End Function


Sub Refresh_Holidays_Taken()
    On Error Resume Next
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    y = ActiveCell.Row
    Sheets("Summary").Cells(y, 6) = Get_Holidays_Taken(y - 3)
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[vletm]

Shazz
There are 52 ... 53 weeks in one Year.
You do not need to have 52-53 times same sheet!
Now, if You need to change something to one sheet then
You need to do same change to all 52-53 sheets ... instead to only one sheet!
>
My sample works in that file.
I cannot know if Your 'real sheet' has even minor difference which could cause interesting affect!
... and
Where did You save those?
... did You changes to all 52 sheets?
As I tried to give a hint ... use one sheet instead those 52.
I have done that kind with many times.
... and You could use same file for many years.
It's Your choice - 'one time work or 52 times work'.

>> Send Your file via conversation ... I could try to check it.