How to populate list of names of worksheets

minsk2000

Board Regular
Joined
Dec 25, 2005
Messages
68
Hi Everyone,

I have to do some calculations with workbooks that have different numbers of worksheets (tabs) with different name.
I would like to create a standard worksheet and add it to each of the workbooks where in the column A I would be able to populate the list of tabs with their names.

Is there any forumula that could do that?

Thanks
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Not really sure I understand what it is you want, but, here is some VB to list each worksheet name in the current workbook

For Each sh In Worksheets
Debug.Print sh.Name
Next

Does this help ?

Chris
 

minsk2000

Board Regular
Joined
Dec 25, 2005
Messages
68
Hi Chris,

Thanks for the reply.
In my workbooks there are some 70 tabs with their names.
I would like to add 71st tab (sheet) and there in the range say A1:A70 get all names of tabs from this workbook.

Some workbooks have 70 sheets others 50, 55 and so on.
So wonder how to get names of this tabs in the additionsl worksheet in a certain pre-defined range.

Thanks,

Minsk
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
OK, add the new sheet and in the VB Editor add this code to the new worksheet object

x=1
For Each sh In Worksheets
cells(1,x)=sh.name
x=x+1
Next

Run the code and you'll see the first 70 cells in your new sheet are populated with the worksheet names in the current workbook

HTH

Chris
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Minsk

You may find this useful:

Code:
Sub SheetMenu()

Dim Wsheet As Worksheet, i As Integer
    
For i = ActiveWorkbook.Sheets.Count To 1 Step -1    'will cycle thru each sheet in workbook
    
    ActiveWorkbook.Names.Add Name:="Namer" & i, _
    RefersToR1C1:="='" & Sheets(i).Name & "'!R1C1"
    'adds names to a workbook referring to cell A1 in each sheet
    'so important not to have any existing names on sheet referring to
    'just cell A1 (doesnt matter if a named range includes A1)
    
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
    Address:="", SubAddress:="Namer" & i
    'adds a hyperlink to the active sheet (so usually need to insert a
    'new sheet to list the links).  Subaddress points at the named
    'ranges just created
    
    ActiveCell.FormulaR1C1 = Sheets(i).Name
    'renames the cell containg the link so that it reflects the
    'relevant sheet name
    
    ActiveCell.Offset(1, 0).Select 'go down a row ready to insert next hyperlink
    

Next i


End Sub

Best regards

Richard
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Here's some code by Zack Baresse (aka Firefytr) that will build a hyperlinked Table of Contents:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> CreateTOC()
    <SPAN style="color:#007F00">'   Code by Zack Baresse</SPAN>
    <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>
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Application
        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    
        <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _
            ct <SPAN style="color:#00007F">As</SPAN> Chart, _
            shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _
            nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        
        nrow = 3
        i = 1
        numCharts = ActiveWorkbook.Charts.Count
        
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet
        Sheets("TOC").Activate
        <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _
        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

hasSheet:
    Sheets.Add Before:=Sheets(1)
    <SPAN style="color:#00007F">GoTo</SPAN> hasNew

createNew:
    Sheets("TOC").Delete
    <SPAN style="color:#00007F">GoTo</SPAN> hasSheet

hasNew:
    tmpCount = ActiveWorkbook.Charts.Count
    <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1
        ActiveSheet.Name = "TOC"
        
        <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
            .Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4
                <SPAN style="color:#00007F">With</SPAN> .Range("B2")
                    .Value = "Table of Contents"
                    .Font.Bold = <SPAN style="color:#00007F">True</SPAN>
                    .Font.Name = "Tahoma"
                    .Font.Size = "24"
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            nrow = nrow + 1
            <SPAN style="color:#00007F">With</SPAN> ws
                shtName = ws.Name
                <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Hyperlinks.Add _
                        Anchor:=Sheets("TOC").Range("C" & nrow), Address:="#'" & _
                        shtName & "'!A1", TextToDisplay:=shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> ws
        
        <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts
                nrow = nrow + 1
                shtName = ct.Name
                <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Value = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> ct
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
            <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")
                .MergeCells = <SPAN style="color:#00007F">True</SPAN>
                .HorizontalAlignment = xlLeft
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
            <SPAN style="color:#00007F">With</SPAN> .Range("C:C")
                .EntireColumn.AutoFit
                .Activate
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            .Range("B4").Select
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _
        "Charts are listed after regular " & vbCrLf & _
        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

(Heya Chris, long time no see!)
 

minsk2000

Board Regular
Joined
Dec 25, 2005
Messages
68

ADVERTISEMENT

Thanks Richard,

This is extremely useful code for my workbooks. I was looking for something like that recently.

Works great!

I'm looking for the same thing but without hyperlinks.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I'm looking for the same thing but without hyperlinks.

Just change the hyperlinked part:

<font face=Tahoma>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            nrow = nrow + 1
            <SPAN style="color:#00007F">With</SPAN> ws
                shtName = ws.Name
                <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow) = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> ws</FONT>

Smitty
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
If anyone's interested in a canned solution, my shareware utilities at excelutilities.com insert a new sheet containing a full list of worksheets in the book.

Just select Utilities - Workbook - Tab Sequence to get the listing. Use the same utility to re-set the sequence of tabs to any desired order.

Also makes it trivial to rename your tabs.
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Hey larry .. post the code for us all to see then.

(thanks smitty, been busy!! back looking around now :) )
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

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
Top