List All Worksheets in Workbook
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.
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
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)
"sparx" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://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., %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
[QUOTE=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
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
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)
"davesexcel" <[email protected]> wrote
in message news:[email protected]...
> >
> > 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
>
>
> --
> davesexcel
> ------------------------------------------------------------------------
> davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
> View this thread: http://www.excelforum.com/showthread...hreadid=517213
>
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)
"sparx" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=517213
>
Yes! That was it,
Re: List All Worksheets in Workbook
Greetings Bob,
I used your code and it works perfectly to do half of what I need. Is there a way for column B to have the data in cell C24 of the corresponding worksheet?
Thanks!
kimpossible
Originally Posted by Bob Phillips
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks