count the sheet in a workbook

[vishalvijayan]

Hello,

I have a excel report generated from a online software. It creates a lot of separate sheets in a workbook. Is there a way to find out how many sheets are there in the workbook.

 

[hallingh]

You can try this:

Sub CountSheets()
Dim SheetNumber As Long
SheetNumber = 0
For Each ws in ActiveWorkbook.Worksheets
      SheetNumber = SheetNumber + 1
Next ws
End Sub

In this method, the variable SheetNumber will end up being however many worksheets there are. You can display this variable in any cell you want so you can see it.

Hank

 

[JackDanIce]

Or try:

Sub CountMySheets ()
Msgbox "Number of sheets in this workbook are: " & Activeworkbook.Worksheets.count
End Sub

Or if you want to output to a cell try:

Range("A1") = Worksheets.Count

 

[vishalvijayan]

is there a way without using macros.