Referencing all sheets in a workbook in VBA
 

Private Sub Counting_Test()

Dim count As Single

count = 0

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

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

--
Message posted from http://www.ExcelForum.com

Referencing all sheets in a workbook in VBA
 

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


--
Regards
Frank Kabel
Frankfurt, Germany


Private Sub Counting_Test()

Dim count As Single

count = 0

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

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
how
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
need to get a way so that every cell between H1 and H275 on every
worksheet in the workbook is counted, not just one worksheet....Can
you all help me?

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

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Referencing all sheets in a workbook in VBA
 

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

--
Message posted from http://www.ExcelForum.com

Referencing all sheets in a workbook in VBA
 

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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany


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


---
Message posted from http://www.ExcelForum.com/