Creating a list of worksheet names on a Summary PAge

[Guest]

I am trying to create a function that will list all of my worksheet names,
one per row, on a summary page. I cannot seem to be able to link the title of
the worksheets to anything? Please help. If this requires some sort of macro,
please be very precise since i have no clue how to use VB. Thanks

 

[Dave Peterson]

You want a worksheet that uses hyperlinks that point at those other sheets?

David McRitchie has a macro that will build that table of contents worksheet:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Guest]

I am not too sure what a hyperlink is but I just wanted to make a list of all
the worksheet names. This in in order to make a matrix with names of
different products in top row, and then the value assigned to each product in
each worksheet. Does that make any sense?

Thanks.

 

[Dave Peterson]

The hyperlink will mean you can click that cell and go to that worksheet.

It might be a nice feature of your table.

 

[Gord Dibben]

confused

Here is a macro that will list all sheets onto a new sheet.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP