another way to get the list
Sub listsheets()
For i = 1 To ActiveWorkbook.Worksheets.Count
Cells(i, "b") = Sheets(i).Name
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub sheets_num()
MsgBox ActiveWorkbook.Sheets.Count & " sheets in workbook"
End Sub

If you also want a list of them..........

Sub ListSheets()
'list of sheet names starting at A1 on a new sheet
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Note: if you have Chart sheets or Dialog sheets, they will be listed but
"List"
sheet will be placed after last worksheet, not necessarily after last
sheet.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

On Sat, 3 May 2008 11:01:01 -0700, Willie

wrote:

How can I count the number of worksheets in a workbook?