VBA: Count the sheets in a Workbook

August 14th, 2004 | Categories: Worksheets | Tags: , ,

If you ever need to count the number of sheets in a workbook, use Application.Sheets.Count

Put this in a module:

Public Sub CountMySheets()
MsgBox Application.Sheets.Count
End Sub
  1. July 27th, 2011 at 10:29
    Reply | Quote | #1

    Dim objExcel As Excel.Application
    Dim objWb As Workbook

    Set objExcel = New Excel.Application
    objExcel.Workbooks.Open “File Path”

    intNoOfSheets = objExcel.Sheets.Count