Count a single cell across multiple sheets

[danturv]

Hi, I will try to explain this as best I can!
I have a workbook to track employees annual leave usage and entitlement, it has a sheet with a list of employees, a summary sheet showing holiday days taken/remaining etc and then a calendar type sheet for each employee with the days they have booked off. I have been asked to create another summary sheet to show how many employees have booked a particular day off, ideally someone could click on cell A5 in the new summary sheet and you get a count and a list of the employees sheets where A5 is not blank

Hopefully that makes sense and thanks in advance for any help offered

 

[ranman256]

the example prints to immediate window (Debug.Print) , you can change it to anywhere.

'assuming the sheet name is the emp name
Dim ws As Worksheet
Dim i As Integer
Dim colEmps As New Collection
For Each ws In Sheets
If ws.Range("A5").Value <> "" Then colEmps.Add ws.Name
Next

For i = 1 To colEmps.Count
Debug.Print colEmps(1)
Next
MsgBox colEmps.Count & " want that day off"
Set colEmps = Nothing