Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically create list of the worksheets
Hello,
I would like to create basically an index or outline page with a list (and hyperlinks if possible) of all of my worksheet titles or names. I found the following macros in previous questions, however, I had these 2 problems: 1) It is not recognizing ALL of my worksheets 2) I do not know how to make sure it constently updates I am a total Macro ROOKIE so please give me the idiot's version of the answer Here is the macro I found and tried to use: Sub Sheet_Names() Dim ws As Worksheet With Worksheets.Add .Name = "Sheet Names" .Move befo=Worksheets(1) End With Sheets("Sheet Names").Activate Range("a1").Activate For Each ws In Worksheets If ws.Name < "Sheet Names" Then ActiveCell.Formula = ws.Name ActiveCell.Offset(1, 0).Select End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically create list of the worksheets
This will list all sheets in the active workbook in col 1 of the activesheet
Sub listsheetsinthisworkbook() For i = 1 To Sheets.count Cells(i, 1) = Sheets(i).Name Next i End Sub Right click sheet tabview codeput the macro below in the same sheetNow when you doubleclick on the sheet you will be taken to it. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then ' GetWorkbook ' calls another macro to do that Else Application.Goto Sheets(WantedSheet).Range("g1") End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Automatically create index of worksheets" <Automatically create index of wrote in message ... Hello, I would like to create basically an index or outline page with a list (and hyperlinks if possible) of all of my worksheet titles or names. I found the following macros in previous questions, however, I had these 2 problems: 1) It is not recognizing ALL of my worksheets 2) I do not know how to make sure it constently updates I am a total Macro ROOKIE so please give me the idiot's version of the answer Here is the macro I found and tried to use: Sub Sheet_Names() Dim ws As Worksheet With Worksheets.Add .Name = "Sheet Names" .Move befo=Worksheets(1) End With Sheets("Sheet Names").Activate Range("a1").Activate For Each ws In Worksheets If ws.Name < "Sheet Names" Then ActiveCell.Formula = ws.Name ActiveCell.Offset(1, 0).Select End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically create list of the worksheets
Not sure why it is not recognizing all worksheets unless some of them are
Chart sheets. Here is a modification to create the hyperlinks you need: Sub Sheet_Names() Dim ws As Worksheet With Worksheets.Add .Name = "Sheet Names" ' .Move befo=Worksheets(1)'sheets always added on the left End With ' Sheets("Sheet Names").Activate 'newly added sheet is the active sheet ' Range("a1").Activate 'automatically active on a new sheet For Each ws In Worksheets If ws.Name < "Sheet Names" Then ActiveCell.Value = ws.Name ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ ws.Name & "!A1", TextToDisplay:=ws.Name ActiveCell.Offset(1, 0).Select End If Next End Sub Notice I commented out some unnecessary lines. A note that this macro will add a new sheet each time it is ran. If you intend to add more sheets and then run this to update your index page then some more code would be needed to check if Sheet Names already exists. Mike F "Automatically create index of worksheets" <Automatically create index of wrote in message ... Hello, I would like to create basically an index or outline page with a list (and hyperlinks if possible) of all of my worksheet titles or names. I found the following macros in previous questions, however, I had these 2 problems: 1) It is not recognizing ALL of my worksheets 2) I do not know how to make sure it constently updates I am a total Macro ROOKIE so please give me the idiot's version of the answer Here is the macro I found and tried to use: Sub Sheet_Names() Dim ws As Worksheet With Worksheets.Add .Name = "Sheet Names" .Move befo=Worksheets(1) End With Sheets("Sheet Names").Activate Range("a1").Activate For Each ws In Worksheets If ws.Name < "Sheet Names" Then ActiveCell.Formula = ws.Name ActiveCell.Offset(1, 0).Select End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically create list of the worksheets
hi
if your are a total macro rookie, you may opt for a non-vba way. right click the sheet navigator in the lower left of any sheet. you will get a popup listing all of your sheets. click on to go to it. if you have more that 15 sheets(i think it 15) you will have an extra entry "more sheets". click it and you will get a popup scrollable text box with all your sheet names. Regards FSt1 "Automatically create index of worksheets" wrote: Hello, I would like to create basically an index or outline page with a list (and hyperlinks if possible) of all of my worksheet titles or names. I found the following macros in previous questions, however, I had these 2 problems: 1) It is not recognizing ALL of my worksheets 2) I do not know how to make sure it constently updates I am a total Macro ROOKIE so please give me the idiot's version of the answer Here is the macro I found and tried to use: Sub Sheet_Names() Dim ws As Worksheet With Worksheets.Add .Name = "Sheet Names" .Move befo=Worksheets(1) End With Sheets("Sheet Names").Activate Range("a1").Activate For Each ws In Worksheets If ws.Name < "Sheet Names" Then ActiveCell.Formula = ws.Name ActiveCell.Offset(1, 0).Select End If Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically create list of the worksheets
Rather than an index sheet with hyperlinks.......how do you get back to the
index sheet?.............try a sheet navigation toolbar or similar. Sheet navigation bar from by Dave Peterson at Debra Dalgleish's site. http://www.contextures.on.ca/xlToolbar01.html Or Bob Phillips' Browsesheets macro. See this google search result. http://tinyurl.com/yoa3dw Gord Dibben MS Excel MVP On Sun, 20 Jan 2008 05:04:03 -0800, Automatically create index of worksheets <Automatically create index of wrote: I would like to create basically an index or outline page with a list (and hyperlinks if possible) of all of my worksheet titles or names. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically create list in different sheet on basis of other lis | Excel Worksheet Functions | |||
Automatically create worksheets | Excel Discussion (Misc queries) | |||
How to Create a list which sort itself automatically | Excel Worksheet Functions | |||
How to automatically create and populate worksheets? | Excel Programming | |||
Create list of worksheets | Excel Discussion (Misc queries) |