Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=517213 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in 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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=517213 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
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 ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=517213 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
Bob Phillips Wrote: 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 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=517213 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
List All Worksheets in Workbook
Yes! That was it,
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Take a list from one workbook and apply asdropdown in another work | Excel Worksheet Functions | |||
How do I build a workbook from the worksheets another workbook? | Excel Discussion (Misc queries) | |||
view list of worksheets contained within a workbook | Excel Worksheet Functions | |||
how do I arrange multiple worksheets from the same workbook | Excel Discussion (Misc queries) | |||
Count the number of worksheets in a workbook | Excel Discussion (Misc queries) |