Any high-speed, low drag Excel rangers up in here?

[Chardok]

Wondering if someone here could point me in the right direction.

I have a spreadsheet with 14 total tabs. 13 that are named for the employees on a team (So sheet 1 is named Jeff smith, for example, sheet 2, Jimmy Jones etc. etc.) sheet 14 is a summary of junk contained in those 13 prior sheets. what I want to do is have Cells A3:A16 in sheet 14 display the names of those tabs. (So Cell A3 displays Jeff Smith, A4 Jimmy Jones, the names of their corresponding tabs)

Any ideas? I bet my problem is that I'm trying to do this the "hard way"...that's usually it.

[General Zod]

Are they going to contain links to their corresponding tabs or are you just trying to create a static directory?

This might be what you're looking for if you have a bit of VBA. http://www.contextures.on.ca/xlToolbar01.html

[Chardok]

nothing fancy, I just want the name of the tab displayed in the cell. doesn't have to be hyperlinked or anything

[General Zod]

Here's about as easy as you can get while staying accurate:

Code: Select all

Sub ListSheets() 
    Dim i   As Byte, j  As Byte 
    Dim Sht() 
     
    Redim Sht(ThisWorkbook.Sheets.Count) 
    For i = 1 To Sheets.Count 
        Sht(j) = Sheets(i).Name 
        j = j + 1 
    Next i 
    Redim Preserve Sht(j) 
    [a1].Resize(UBound(Sht), 1) = Application.Transpose(Sht) 
End Sub 

Go to the tab you want your directory.

Go to "developer" in the ribbon, and select "Visual Basic".

Paste in the code above to the vb form. Make sure you have the tab selected where you want it to run, it should now look like this:



Go under "run" in visual basic, and select "run sub/user form" after you save it. If it doesn't let you save then you need to change your Excel file type.

[Chardok]

Rockin' Saved me arse, yeh did.