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

try this
Sub howmanyworksheets()
MsgBox ActiveWorkbook.Worksheets.Count
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Willie" wrote in message
...
How can I count the number of worksheets in a workbook?

=COUNTA(Sheet1:Sheet10!A1)

Where Sheet1 is the name of your first worksheet, Sheet10 is the name of
your last worksheet, and A1 is a cell that contains content on every page.
You can enter this using the point method, if you type
=counta(
click on the first sheet tab, hold down your shift key and click on the last
sheet tab, and the click the cell that contains content - A1 works well if
you have a title on every page.
type ) and Enter.

"Willie" wrote:

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

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?

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?

If you just have worksheets.....yes.

Otherwise

Sub listsheets22()
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(i, "b") = Sheets(i).Name
Next i
End Sub


Gord


On Sat, 3 May 2008 13:23:53 -0500, "Don Guillett"
wrote:

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

Good Gord.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If you just have worksheets.....yes.

Otherwise

Sub listsheets22()
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(i, "b") = Sheets(i).Name
Next i
End Sub


Gord


On Sat, 3 May 2008 13:23:53 -0500, "Don Guillett"

wrote:

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

Another way - from the menus choose...

help about Excel system info office apps Excel active workbook

this lists sheet count and names. (Copy by ctrl-clicking items.)

Or... similar to the formula above, enter in any cell: =counta('*'!a1)+1
[since the '*' evaluates to all sheets other than the active one.]

"Willie" wrote:

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

Lori,
Shouldn't that formula:

=counta('*'!a1)+1

be accompanied with a caveat that there's a bug in XL02 where that formula
*might* cause a crash, and/or a mis-calculation?

Don't know what it does in XL03 and XL07.

Does work fine in XL97.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lori" wrote in message
...
Another way - from the menus choose...

help about Excel system info office apps Excel active workbook

this lists sheet count and names. (Copy by ctrl-clicking items.)

Or... similar to the formula above, enter in any cell: =counta('*'!a1)+1
[since the '*' evaluates to all sheets other than the active one.]

"Willie" wrote:

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

Oops, you're right i use xl2003 which works fine but i seem to remember from
before a bug in xl2002 when you try to edit such a formula that can cause a
crash.

Pasting the formula directly into a cell as text should be fine in all
versions although probably better to be safe and avoid this functionality
with xl2002 though ;-)

"Ragdyer" wrote:

Lori,
Shouldn't that formula:

=counta('*'!a1)+1

be accompanied with a caveat that there's a bug in XL02 where that formula
*might* cause a crash, and/or a mis-calculation?

Don't know what it does in XL03 and XL07.

Does work fine in XL97.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lori" wrote in message
...
Another way - from the menus choose...

help about Excel system info office apps Excel active workbook

this lists sheet count and names. (Copy by ctrl-clicking items.)

Or... similar to the formula above, enter in any cell: =counta('*'!a1)+1
[since the '*' evaluates to all sheets other than the active one.]

"Willie" wrote:

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