Referencing all sheets in a workbook in VBA

[abxy]

Private Sub Counting_Test()

Dim count As Single

count = 0

For Each c In Workbooks("June.xls").Worksheets("11").Range("H1:H275")

If c.Value = Date Then count = count + 1

Next

MsgBox count

End Sub


Ok, when this macro is run, it'll give me a message box telling me ho
many cells in workbook "June" bewteen H1 and H275 on worksheet "11
have today's date in the in them. This works just fine, however, I nee
to get a way so that every cell between H1 and H275 on every workshee
in the workbook is counted, not just one worksheet....Can you all hel
me?

PS. the "Sheets(Array" term can't be used because that always refers t
specific sheets. This workbook is updated daily, so I can't refer t
specific sheets.

Thanks in advanc

 

[Frank Kabel]

Hi
try

Private Sub Counting_Test()
Dim wks as worksheet
Dim count As Single

count = 0
for each wks in activeworkbook.worksheets
count = count + application.countif(wks.range("H1:H275"),Date)
next
MsgBox count
End Sub

 

[abxy]

Hah!, where's like a charm! thanks Frank! :

 

[Frank Kabel]

Hi
thanks for your feedback. Just as additional note: This could also be
achieved with (complicated) worksheet functions. But if you're happy
with VBA, use it