List All Worksheets in Workbook

[sparx]

Can anybody help by providing a formula or macro that will list all th
worksheets in a workbook regardless if I add some new worksheets at
later stage - I want to be able to see in one sheet - all the availabl
worksheets that make up my workbook

 

[Bob Phillips]

Here is some code

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ListSheets
End Sub

Private Sub Workbook_Open()
List Sheets
End Sub

Private Sub ListSheets()
Dim wsh As Worksheet
Dim Sh As Object
Dim i As Long

Application.ScreenUpdating = True
Application.EnableEvents = False

On Error Resume Next
Set wsh = Worksheets("ListAll")
On Error GoTo 0

On Error GoTo ListSheets_exit

If Not wsh Is Nothing Then
wsh.Cells.ClearContents
Else
Set wsh = Worksheets.Add
wsh.Name = "ListAll"
End If

For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> wsh.Name Then
i = i + 1
wsh.Cells(i, "A").Value = Sh.Name
End If
Next Sh

wsh.Activate

Set wsh = Nothing
Set Sh = Nothing

ListSheets_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dav]

If you create a sheet on which the list will appear as the first sheet
in your workbook, the following Macro will work

Sub Countsheets()
Sheets(1).Select

For x = 2 To Sheets.Count
Cells(x, 1).Select
Selection.Value = UCase(Sheets(x).Name)
Next x
End Sub

If you wish for the first sheet to be included change x=2 to x=1

You could set it up on a button on the first sheet so you click the
button to update the sheet


Regards

Dav

 

[davesexcel]

here is a great code from two greats!
insert this code in a module and assign a button to it, even create a
button in the toolbars.
When you click on the button a list of your sheets will pop up, you can
even click on one of the sheets in the list and you will go there.


Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11
'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
On Error GoTo 0
End Sub



for more on sheets check out this site
http://www.mvps.org/dmcritchie/excel/sheets.htm






Here's another code you might like, insert it in your worksheet module
Whatever the value you have in Cell A1, will be your sheet name, just
interesting!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
If Target.Value <> "" Then
Me.Name = Target.Value
End If
End If
End Sub

 

[davesexcel]

Here is some code

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ListSheets
End Sub

Private Sub Workbook_Open()
List Sheets
End Sub

Private Sub ListSheets()
Dim wsh As Worksheet
Dim Sh As Object
Dim i As Long

Application.ScreenUpdating = True
Application.EnableEvents = False

On Error Resume Next
Set wsh = Worksheets("ListAll")
On Error GoTo 0

On Error GoTo ListSheets_exit

If Not wsh Is Nothing Then
wsh.Cells.ClearContents
Else
Set wsh = Worksheets.Add
wsh.Name = "ListAll"
End If

For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> wsh.Name Then
i = i + 1
wsh.Cells(i, "A").Value = Sh.Name
End If
Next Sh

wsh.Activate

Set wsh = Nothing
Set Sh = Nothing

ListSheets_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips


Private Sub Workbook_Open()
List Sheets
End Sub

Hi Bob

I get a sub not defined at this point, when I open the workbook, should
the list sheets macro be in a regular module?
nope just tried it, still says undefined sub or function

 

[Bob Phillips]

No, it is fine in Thisworkbook, but the workbook procedures must be in
ThisWorkbook as shown, but it would help if I hadn't included a space in the
open procedure


Private Sub Workbook_Open()
ListSheets
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Arvi Laanemets]

Hi

Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
TabI = Sheets(TabIndex).Name
End Function


In a column, you use this UDF to return 1st, 2nd, etc sheet name - like this

A1=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
(and copy down)

 

[davesexcel]

Yes! That was it,