Thread
:
automatically create list of the worksheets
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett