Microsoft Excel - Displaying all ws names in a summary ws cell, then accessing each ws by clicking that cell

Thanks in advance.

Simple workbook that has worksheets for each contracting job that i do.  Each ws is named for that particular customer i.,e. John Smith.  The first ws is a summary ws that has sales #'s, etc.  carried over from each independent ws but I would like to have the name of each ws appear in the summary ws when i create that ws, instead of me manually entering it.

The second part of this question is: can i then click on the cell that has the name of the ws to access that specific ws?

For example:  summary ws cell # A100 has the name of the 100th ws in this wb, JOhn Smith, I would like to then be able to click on , John Smith (A100), and be able to go directly to ws John Smith w/o have to scroll through at the bottom.

I use this workbook each year and have it built with 200 ws already and just name them when I complete each job.
Excel 2010

Thank you,
Mark

Hi Mark,
I have done pretty much that with the following macro.  You will obviously have to adjust the addressing to suit your particular circumstance as I have listed the sheets in columns to fit the screen etc.  I also matched the font colour to the worksheet tab colour if that is useful to you.  On each worksheet there was also a cell with a hyperlink that returned you to the menu sheet

Sub TableOfContents()

Dim wks As Worksheet
Dim rngLinkCell As Range
Dim rngNewCell As Range
Dim strSubAddress As String
Dim strDisplayText As String
Dim intCount As Integer
Dim intOffsetR As Integer
Dim intOffsetC As Integer

Application.ScreenUpdating = False

'Clear current TOC
Worksheets("Menu").Range("A7:X26").ClearContents

'Create new TOC
intCount = 0
Set rngLinkCell = Worksheets("Menu").Range("A7")
For Each wks In Worksheets
    intOffsetR = intCount Mod 20
    intOffsetC = Int(intCount / 20) * 6
    rngLinkCell.Offset(intOffsetR, intOffsetC).Value = intCount + 1
    Set rngNewCell = rngLinkCell.Offset(intOffsetR, intOffsetC + 2)
    strSubAddress = "'" & wks.Name & "'!A1"
    strDisplayText = wks.Range("E1").Text
    Worksheets("Menu").Hyperlinks.Add _
    anchor:=rngNewCell, _
    Address:="", _
    SubAddress:=strSubAddress, _
    TextToDisplay:=strDisplayText
    If wks.Tab.ColorIndex = xlColorIndexNone Then
    rngNewCell.Font.ColorIndex = 7
    Else
    rngNewCell.Font.ColorIndex = wks.Tab.ColorIndex
    End If
    intCount = intCount + 1
Next wks

Application.ScreenUpdating = True

ActiveSheet.Range("J1").Value = Date

End Sub

Harry....'

thanks for taking the time to do this.

I'm a little bit over a novice level so pardon some quick basic questions if you don't mind:

I inserted the macro as is and obviously came back with an run time error 9, subscript out of range,  when i debug it shows this highlighted in yellow:

'Clear current TOC
Worksheets("Menu").Range("A7:X26").ClearContents

not sure what i need to change it too,

I inserted this in a brand new document  too and it came back the same.


what would i need to change it to?

My summary sheet only has a few columns to track info:
Column A = Date
Column B= Customer name
the rest just have sales #'s so only B will be where the info will change.
Also, is this the only way to do this with a macro?  Or can the cells in column B be set up initially to automatically change when I rename the ws.  Maybe i will understand more / better when I see how you set it up.

date	Name	sales
January 20, 2012	Smith, John	1126.84
February 28, 2012	Phillips, Ryan & Maygen	1650.5

If you could walk me through the steps to get this working.

I open the macro box in developer and create a new macro and save it as an excel macro-enabled file type.
When I run it with the new document I created it comes back with the same error.

Hope this makes some sense,
Thanks again,
Mark

Hi Mark
Create a new Tab and name it Menu or replace the name in the macro to the new sheet name.
It's giving you an error because it's looking for Worksheets called Menu that you don't have.

Hi Mark,

As John D says it is probably looking for a sheet called Menu.  Just change all of the "Menu" references in the macro to the name of your summary sheet.  Also change the range references "A7:A26" to "B?:B???" to suit your requirements.  As it will all be in one column there are some other changes as below.

I have also commented out the entry of the date which was just to show when the TOC was last updated.  You can restore that if you wish to an appropriate cell by changing the cell reference and removing the comment mark at the beginning of the line.

The hyperlink will take you to cell A1 on the relevant sheet.  if you want to navigate to somewhere else then change that reference.

I don't think it is possible to do what you want by using formulae.  If your working sheets all have the same layout, it would be possible to fill in the whole summary page using a macro to save all the between page references.

Regards

Harry

Sub TableOfContents()

Dim wks As Worksheet
Dim rngLinkCell As Range
Dim rngNewCell As Range
Dim strSubAddress As String
Dim strDisplayText As String
Dim intCount As Integer
Dim intOffsetR As Integer
Dim intOffsetC As Integer

Application.ScreenUpdating = False

'Clear current TOC
Worksheets("Your SummarySheetNameHere").Range("B?:B???").ClearContents

'Create new TOC
intCount = 0
Set rngLinkCell = Worksheets("YourSummarySheetNameHere").Range("B?")
For Each wks In Worksheets
    intOffsetR = intCount
    intOffsetC = 0
    Set rngNewCell = rngLinkCell.Offset(intOffsetR, intOffsetC )
    strSubAddress = "'" & wks.Name & "'!A1"
    strDisplayText = wks.Name
    Worksheets("Menu").Hyperlinks.Add _
    anchor:=rngNewCell, _
    Address:="", _
    SubAddress:=strSubAddress, _
    TextToDisplay:=strDisplayText
    If wks.Tab.ColorIndex = xlColorIndexNone Then
    rngNewCell.Font.ColorIndex = 7
    Else
    rngNewCell.Font.ColorIndex = wks.Tab.ColorIndex
    End If
    intCount = intCount + 1
Next wks

Application.ScreenUpdating = True

'ActiveSheet.Range("J1").Value = Date

End Sub

Hi Mark,
I noticed that in my last post (that I don't now seem to be able to edit even though the edit tag shows) that I missed one occurrence of the "Menu" worksheet name that needs to be changed.
Also I didn't mention that you should put a navigation hyperlink on each of your working pages to get back easily to your summary sheet.  Just select a suitable cell somewhere and use Insert/Hyperlink to give it a suitable name and to point back to your summary sheet.  Unfortunately to do it this way you have to do it one sheet at a time but it would also be possible to copy and modify the macro to do it automatically.  This would be a once off job.  When I get a moment I will modify the code and post it for you.
Regards
Harry

Hello again Mark,
Here is the code to put return links on every working sheet.

Sub ReturnLinks()

Dim wks As Worksheet
Dim strSubAddress As String
Dim strDisplayText As String

Application.ScreenUpdating = False

'Create new Return Link
For Each wks In Worksheets
If wks.Name <> "YourSummarySheetNameHere" Then
    strSubAddress = "'YourSummarySheetNameHere'!A1"
    strDisplayText = "HOME"
    wks.Hyperlinks.Add _
    anchor:=wks.Range("A1"), _
    Address:="", _
    SubAddress:=strSubAddress, _
    TextToDisplay:=strDisplayText
End If
Next wks

Application.ScreenUpdating = True

End Sub

This code places the link in cell A1 on each sheet (other than your summary sheet) and labels it HOME if there is nothing else in the cell.  The link returns you to A1 on your summary sheet.  You can change those parameters if you would like something else.

You should also put in the if wks.Name <> ...end if around the code inside the For Each loop in the TableOfContents macro to prevent it making a self reference as well.

Hope you can manage all this. Let me know if not.

Harry

Hi Mark,
Managed to reply to myself.  Please see latest post.
Harry

Harry.....

Thank you so much for the time and effort it is truly appreciated.

I did inset the first  macro and it worked perfectly!

I will try the code below shortly and let you know if there are any issues i cannot correct on this end or if there are any other questions, which there will probably be , ha !

Thanks again,
Mark