Count all worksheets?

mjd443

Suppose that you have a lot of Excel files (workbooks) currently open. You would like to count the number of these files and also total up the number of worksheets each of these files contains. Write a sub that will perform this task and report the results in a message box.

I understand how to count all workbooks...but how do I count all worksheets within the open workbooks?

Please help, thank you!

tstav

Returns the number of worksheets per workbook, the total number of workbooks and the total number of worksheets.

[code]Sub CountWorkbooks_Worksheets()
Dim wb As Workbook, ws As Worksheet
Dim wbCount, wsCount, totwsCount As Integer
Dim message As String
For Each wb In Workbooks
wbCount = wbCount + 1
wsCount = 0
For Each ws In wb.Worksheets
wsCount = wsCount + 1
totwsCount = totwsCount + 1
Next 'ws
message = message & "Wbk " & wb.Name & ": " & _
wsCount & " Worksheet(s)" & vbCrLf
Next 'Wb
message = message & "Total Workbooks: " & wbCount & _
", Total WorkSheets: " & totwsCount
MsgBox message, , "Result"
End Sub[/code]