| Home |
| Search |
| Today's Posts |
|
#5
|
|||
|
|||
Works perfectly now, thanks indeed Jim !
======== "Jim Cone" wrote: Phil, The last sheet is purposely omitted in this line... For a = 1 To Sheets.Count - 1 Remove the "-1" to include the last sheet. Regards, Jim Cone San Francisco, USA "Phil Osman" wrote in message ... Jim, That works well, but the sheet I am returning the list to is the 2nd sheet in the workbook. Maybe because of this the macro does not return the last sheet in the workbook. Any ideas? Phil "Jim Cone" wrote: Phil, I assume you have the list on the last sheet in the workbook. With some modification to your code, I came up with the following. Note: that Xl97 hyperlink code would be slightly different and that you cannot hyperlink to a chart sheet... '-------------------------------------------------- Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 13 For a = 1 To Sheets.Count - 1 shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub '------------------------------------------------ Regards, Jim Cone San Francisco, USA "Phil Osman" wrote in message ... I have the following code: Sub sheets_list() Range("M1:M100").Select Selection.ClearContents Range("M1").Select r = ActiveCell.Row s = ActiveCell.Column For a = 0 To Sheets.Count - 1 Cells(r + a, s).Value = Sheets(a + 1).Name Next a End Sub It returns a list of all sheets in my workbook. Is it possible to amend the code so that it returns each of the sheet names as a hyperlink to the corresponding sheet ? TIA, Phil |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hyperlink question | Excel Discussion (Misc queries) | |||
| Hyperlink question | Excel Worksheet Functions | |||
| Removing hyperlink | Excel Discussion (Misc queries) | |||
| Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel | |||
| hyperlink question | Excel Worksheet Functions | |||
Threaded Mode
