Count all worksheets?

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!


  • 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]
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion