Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a list of Tab names (2003)

    Hi all,
    I have a workbook with about 80 work sheets in it. I am trying to create another worksheet that lists downwards from cell A1, the name of each of the other 80 worksheets?
    Is this possible?
    Thanks.

    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a list of Tab names (2003)

    See the various replies to <post:=642,669>post 642,669</post:>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a list of Tab names (2003)

    Morning,

    I have not read through all of the thread that Hans has pointed to, would it not be easy to use something like the free ASAP utilty http://www.asap-utilities.com/ which will create an index page with all of your tabs hyperlinked?

    My inexperience with Excel has probably lead me to misunderstanding your requirements but I hope it helps

    Cheers

    Steve

    (Editted because they way it read it suggested that you should not bother following Han's link which was not my intention)
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a list of Tab names (2003)

    This will create a tab to the left of the existing tabs called TOC. It also creates a hyperlink to th A1 cell of each sheet. Remove the lines in blue if you don't want hyperlinks.

    Sub HypeToTabs()
    Dim i As Integer, chk As Boolean, TabName As String, TabCount As Integer


    'checks for TOC sheet
    For i = ActiveWorkbook.Sheets.count To 1 Step -1
    If ActiveWorkbook.Sheets(i).Name = "TOC" Then
    chk = True
    Exit For
    End If
    Next

    'adds TOC if it does not exist
    If chk <> True Then
    With ActiveWorkbook.Worksheets.Add(before:=Worksheets(1 ))
    .Name = "TOC"
    End With
    End If

    Worksheets("TOC").Activate
    Cells(1,1) = "Tab Name"
    TabCount = Worksheets.count
    For i = TabCount To 2 Step -1
    Cells(i, 1) = Worksheets(i).Name

    <font color=blue>

    With Cells(i, 1)
    .Hyperlinks.Add anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Worksheets(i).Name & "'!A1"
    End With
    </font color=blue>
    Next i


    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •