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

GEC: Discuss gaming, computers and electronics and venture into the bizarre world of STGODs.

Moderator: Thanas

Post Reply
User avatar
Chardok
GET THE FUCK OFF MY OBSTACLE!
Posts: 8488
Joined: 2003-08-12 09:49am
Location: San Antonio

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

Post by 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.
Image
User avatar
General Zod
Never Shuts Up
Posts: 29205
Joined: 2003-11-18 03:08pm
Location: The Clearance Rack
Contact:

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

Post by 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
"It's you Americans. There's something about nipples you hate. If this were Germany, we'd be romping around naked on the stage here."
User avatar
Chardok
GET THE FUCK OFF MY OBSTACLE!
Posts: 8488
Joined: 2003-08-12 09:49am
Location: San Antonio

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

Post by Chardok »

nothing fancy, I just want the name of the tab displayed in the cell. doesn't have to be hyperlinked or anything :D
Image
User avatar
General Zod
Never Shuts Up
Posts: 29205
Joined: 2003-11-18 03:08pm
Location: The Clearance Rack
Contact:

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

Post by 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:

Image

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.
"It's you Americans. There's something about nipples you hate. If this were Germany, we'd be romping around naked on the stage here."
User avatar
Chardok
GET THE FUCK OFF MY OBSTACLE!
Posts: 8488
Joined: 2003-08-12 09:49am
Location: San Antonio

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

Post by Chardok »

Rockin' Saved me arse, yeh did.
Image
Post Reply