How do I extract all Sheet names

dano_1352

dano_1352

New Member
Joined
Nov 8, 2006
Messages
33
I have a workbook with 40 or so worksheet tabs in it. How do I extract the names of these different tabs into, lets say Column A?? Anyone??
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Run this on a blank sheet

Code: 
Sub SheetNames()
x = 1
For Each ws in Sheets
    Cells(x,"A").Value = ws.Name
    x = x + 1
Next ws
End Sub
 
Upvote 0
One very good add-in called ASAP Utilities (ASAP-Utilities.com) has a handy feature under the Sheets option, #6 which will generate a new worksheet/tab with the names of all the sheet names. For a large spreadsheet, the handy thing is that it's clickable so you can go to any tab quickly. Over 200 utilties as I recall. And, it's free, but you have to update it every 6 months.
Sandman
 
Upvote 0
Jonmo:
I like the code. Is there a way, either in the code or via the menus to make these all hyperlinked to the appropriate tab. The usual hyperlink option appears to only allow it for one cell as you cannot select more than one tab in Edit Hyperlink window (or maybe there is and I'm missing it....
Sandman
 
Upvote 0
You mean you want the list of sheet names to go into a specific sheet?
Rich (BB code): 
Sub SheetNames()
x = 1
For Each ws in Sheets
    Sheets("SheetName").Cells(x,"A").Value = ws.Name
    x = x + 1
Next ws
End Sub
 
Upvote 0
Jonmo:
No, I want each sheet name that's created to be hyperlinked so that when you click on it, you are taken to the corresponding tab. So, you set up a new sheet at the beginning of the spreadsheet (first one on the left), have a list of all the tab names (which your code generates), but each of those tab names is hyperlinked so that when you click on the tab name you are automatically taken to that tab (rather than having to scroll thru all the tabs looking for the one you want).

I've created these hyperlinks manually, one at a time, and then put a similar hyperlink at Cell A-1 of all the other sheets which then takes me back to the first tab where the index is (and I can go to other tabs from there). For a big spreadsheet with 40+ tabs, it helps you find the tabs you want and get around very quickly (particularly if you're not familiar with the spreadsheet).

Hope this is clear.
Sandman
 
Upvote 0
Try right clicking on the little navigation arrows at the bottom left corner of excel (to the left of the sheet tabs).

No need to reinvent the wheel.
 
Upvote 0
Sandman

Hope you dont mind me butting in i have some code already which does what you require:

Code: 
Sub HyperLink_SheetsToIndex()
     
     '   Local  Variables
    Dim wks                 As Worksheet
    Dim rngLinkCell         As Range
    Dim strSubAddress       As String, strDisplayText       As String
     
     '    Step 1 :  Loop through all worksheets
     ' 1a : Clear all current  hyperlinks
    Worksheets("Sheet1").Range("A:A").ClearContents
     ' 1b : Create Linked index list
    For Each wks In ActiveWorkbook.Worksheets
        Set rngLinkCell = Worksheets("sheet1").Range("A65536").End(xlUp)
        If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
        strSubAddress = "'" & wks.Name & "'!A1"
        strDisplayText = wks.Name
        Worksheets("sheet1").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
    Next wks
     
End Sub

I didnt write this code, i just know it was provided by someone on this forum!

Put this in the this workbook mdule and run it.

Hope it helps.
 
Upvote 0
Modifying Jonmo's code I think you are asking for something like this:
Code: 
Sub SheetNames()
x = 1
For Each ws In Sheets
    If ws.Name <> "SheetName" Then
    Sheets("SheetName").Cells(x, "A").Hyperlinks.Add Anchor:=Cells(x, "A"), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    x = x + 1
    End If
Next ws
End Sub
Hope that helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Code that extracts multiple tabs at a time based on lookup tab
2
Replies
10
Views
557
NoSparks
N
A
  • Solved
count how many times text string appears in column
Replies
2
Views
222
ajm
A
E
  • Solved
VBA to extract info from one tab in several workbooks
Replies
8
Views
309
elpepe1970
E
P
  • Question Question
How to extract words in between parenthesis and create a new tab with that name
Replies
7
Views
252
jolivanes
jolivanes
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
250
henrik2h
H

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top