counting worksheets in a book

[Willie]

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

 

[Don Guillett]

try this
Sub howmanyworksheets()
MsgBox ActiveWorkbook.Worksheets.Count
End Sub

 

[BoniM]

=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.

 

[Gord Dibben]

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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord Dibben MS Excel MVP

 

[Don Guillett]

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

 

[Gord Dibben]

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

 

[Don Guillett]

Good Gord.

 

[Lori]

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.]

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

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.]

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

 

[Lori]

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 ;-)

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 !
---------------------------------------------------------------------------

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.]

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