software

Can anybody help by providing a formula or macro that will list all the
worksheets in a workbook regardless if I add some new worksheets at a
later stage - I want to be able to see in one sheet - all the available
worksheets that make up my workbook.--
sparx
------------------------------------------------------------------------
sparx's Profile: www.excelforum.com/member.php...oamp;userid=16787
View this thread: www.excelforum.com/showthread...hreadid=517213Here 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(quot;ListAllquot;)
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 = quot;ListAllquot;
End If

For Each Sh In ThisWorkbook.Sheets
If Sh.Name lt;gt; wsh.Name Then
i = i 1
wsh.Cells(i, quot;Aquot;).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)

quot;sparxquot; gt; wrote in
message ...
gt;
gt; Can anybody help by providing a formula or macro that will list all the
gt; worksheets in a workbook regardless if I add some new worksheets at a
gt; later stage - I want to be able to see in one sheet - all the available
gt; worksheets that make up my workbook.
gt;
gt;
gt; --
gt; sparx
gt; ------------------------------------------------------------------------
gt; sparx's Profile:
www.excelforum.com/member.php...oamp;userid=16787
gt; View this thread: www.excelforum.com/showthread...hreadid=517213
gt;

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 sheetRegards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=517213
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., #ByZYZ3fCHA.1308@tkmsftngp11
'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0@msn.com
On Error Resume Next
Application.CommandBars(quot;Workbook Tabsquot;).Controls(quot;More
Sheets...quot;).Execute
If Err.Number gt; 0 Then
Err.Clear
Application.CommandBars(quot;Workbook Tabsquot;).ShowPopup
End If
On Error GoTo 0
End Sub
for more on sheets check out this site
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 gt; 1 Then Exit Sub
If Target.Address = quot;$A$1quot; Then
If Target.Value lt;gt; quot;quot; Then
Me.Name = Target.Value
End If
End If
End Sub--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=517213
Bob Phillips Wrote:
gt; Here is some code
gt;
gt; Option Explicit
gt;
gt; Private Sub Workbook_NewSheet(ByVal Sh As Object)
gt; ListSheets
gt; End Sub
gt;
gt; Private Sub Workbook_Open()
gt; List Sheets
gt; End Sub
gt;
gt; Private Sub ListSheets()
gt; Dim wsh As Worksheet
gt; Dim Sh As Object
gt; Dim i As Long
gt;
gt; Application.ScreenUpdating = True
gt; Application.EnableEvents = False
gt;
gt; On Error Resume Next
gt; Set wsh = Worksheets(quot;ListAllquot;)
gt; On Error GoTo 0
gt;
gt; On Error GoTo ListSheets_exit
gt;
gt; If Not wsh Is Nothing Then
gt; wsh.Cells.ClearContents
gt; Else
gt; Set wsh = Worksheets.Add
gt; wsh.Name = quot;ListAllquot;
gt; End If
gt;
gt; For Each Sh In ThisWorkbook.Sheets
gt; If Sh.Name lt;gt; wsh.Name Then
gt; i = i 1
gt; wsh.Cells(i, quot;Aquot;).Value = Sh.Name
gt; End If
gt; Next Sh
gt;
gt; wsh.Activate
gt;
gt; Set wsh = Nothing
gt; Set Sh = Nothing
gt;
gt; ListSheets_exit:
gt; Application.EnableEvents = True
gt; Application.ScreenUpdating = True
gt;
gt; End Sub
gt;
gt; 'This is workbook event code.
gt; 'To input this code, right click on the Excel icon on the worksheet
gt; '(or next to the File menu if you maximise your workbooks),
gt; 'select View Code from the menu, and paste the code
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt;
gt; Private Sub Workbook_Open()
gt; List Sheets
gt; End Sub
gt;
gt; Hi Bob
gt;
gt; I get a sub not defined at this point, when I open the workbook, should
gt; the list sheets macro be in a regular module?
gt; nope just tried it, still says undefined sub or function--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=517213No, 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 procedurePrivate Sub Workbook_Open()
ListSheets
End Sub--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;davesexcelquot; gt; wrote
in message ...
gt; gt;
gt; gt; Hi Bob
gt; gt;
gt; gt; I get a sub not defined at this point, when I open the workbook, should
gt; gt; the list sheets macro be in a regular module?
gt; gt; nope just tried it, still says undefined sub or function
gt;
gt;
gt; --
gt; davesexcel
gt; ------------------------------------------------------------------------
gt; davesexcel's Profile:
www.excelforum.com/member.php...oamp;userid=31708
gt; View this thread: www.excelforum.com/showthread...hreadid=517213
gt;
Hi

Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
TabI = Sheets(TabIndex).Name
End FunctionIn a column, you use this UDF to return 1st, 2nd, etc sheet name - like this

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

quot;sparxquot; gt; wrote in
message ...
gt;
gt; Can anybody help by providing a formula or macro that will list all the
gt; worksheets in a workbook regardless if I add some new worksheets at a
gt; later stage - I want to be able to see in one sheet - all the available
gt; worksheets that make up my workbook.
gt;
gt;
gt; --
gt; sparx
gt; ------------------------------------------------------------------------
gt; sparx's Profile:
gt; www.excelforum.com/member.php...oamp;userid=16787
gt; View this thread: www.excelforum.com/showthread...hreadid=517213
gt;
Yes! That was it,