Gather worksheet names

[canadian86]

Hi,

I have 50 worksheets that are named after different cities in the same spreadsheet. (i.e. Toronto, Ottawa, etc)

I need to extract all the 50 worksheet names from my spreadsheet and list them in a specific column.

Any ideas how I can do this?

Thanks!

 

[Smitty]

Zack Barresse's Table of Contents will do it, and it will hyperlink to each sheet:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CreateTOC()<br>    <SPAN style="color:#007F00">'   Code by Zack Baresse</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <br>        <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>            ct <SPAN style="color:#00007F">As</SPAN> Chart, _<br>            shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>            nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        nrow = 3<br>        i = 1<br>        numCharts = ActiveWorkbook.Charts.Count<br>        <br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br>        Sheets("Table of Contents").Activate<br>        <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _<br>        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>hasSheet:<br>    Sheets.Add Before:=Sheets(1)<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasNew<br><br>createNew:<br>    Sheets("Table of Contents").Delete<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br><br>hasNew:<br>    tmpCount = ActiveWorkbook.Charts.Count<br>    <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1<br>        ActiveSheet.Name = "Table of Contents"<br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#007F00">'.Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> .Range("B2")<br>                    .Value = "Table of Contents"<br>                    .Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>                    .Font.Name = "Calibri"<br>                    .Font.Size = "24"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            nrow = nrow + 1<br>            <SPAN style="color:#00007F">With</SPAN> ws<br>                shtName = ws.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Hyperlinks.Add _<br>                        Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _<br>                        shtName & "'!A1", TextToDisplay:=shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <br>        <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts<br>                nrow = nrow + 1<br>                shtName = ct.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Value = shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> ct<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")<br>                .MergeCells = <SPAN style="color:#00007F">True</SPAN><br>                .HorizontalAlignment = xlLeft<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>            <SPAN style="color:#00007F">With</SPAN> .Range("C:C")<br>                .EntireColumn.AutoFit<br>                .Activate<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Range("B4").Select<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _<br>        "Charts are listed after regular " & vbCrLf & _<br>        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[canadian86]

Is there a non-VBA way to do this? I use Excel 2007.

 

[Jeffrey Smith]

Try this:

Sub ListSheetNames()
  Dim WS As Worksheet
  Dim R As Range
  Dim X As Long
  
  Set R = ActiveCell
  X = -1
  
  For Each WS In ActiveWorkbook.Worksheets
    X = X + 1
    R.Offset(X, 0) = WS.Name
  Next WS
End Sub

Past the code into a module.

Jeff

 

[Smitty]

It could be possible with formulas, but I don't know why you would want to do that when VBA is so much more efficient for this.

One shortcut is to right-click on any of the navigation buttons on the bottm left. Clicking on any sheet name in the list will activate it.

 

[canadian86]

I'll try that. I'm using Excel 2007... do u know how I run VBA on it? Where do I paste your code?

 

[Smitty]

Hit ALT+F11 to open the VB Editor, then Insert-->Module and paste the code in the new window that opens on the right. You can run the code by hitting F5 with your cursor anywhere in the code, or ALT+Q will exit you back to Excel, where ALT+T+M+M will bring up the macro window where you can run it.

 

[mazkot]

ASAP Utilities is a free download and has something to do this easily...