ron
04-28-2009, 07:53 AM
Hi
What am I doing wrong? The purpose of the macro is to create links in a sheet named Index to all of the sheets in the workbook. The code compiles and runs but no results.
Thanks
Ron
Sub IndexLinks()
Dim WB As Workbook
Dim ws As Worksheet
Dim i As Long
Dim rCount As Long
Dim CurSheet As String
Dim strSubAdress As String
Dim strDisplayText As String
On Error Resume Next
Application.ScreenUpdating = False
Set WB = ActiveWorkbook
Set ws = Worksheets("Index")
rCount = 1
ws.Rows("1:100").Delete
For i = Sheets("Index").Index + 1 To WB.Sheets.Count
Sheets(i).Select
CurSheet = ActiveSheet.Name
SubAddress = "='" & CurSheet & "'!$A$2"
DisplayText = CurSheet
Worksheets("Index").Hyperlink.Add Anchor:=Cells(rCount, 1), _
Address:="", SubAddress:=strSubAddress, _
TextToDisplay:=strDisplayText
rCount = rCount + 1
Next i
Sheets("Index").Select
Application.ScreenUpdating = True
End Sub
What am I doing wrong? The purpose of the macro is to create links in a sheet named Index to all of the sheets in the workbook. The code compiles and runs but no results.
Thanks
Ron
Sub IndexLinks()
Dim WB As Workbook
Dim ws As Worksheet
Dim i As Long
Dim rCount As Long
Dim CurSheet As String
Dim strSubAdress As String
Dim strDisplayText As String
On Error Resume Next
Application.ScreenUpdating = False
Set WB = ActiveWorkbook
Set ws = Worksheets("Index")
rCount = 1
ws.Rows("1:100").Delete
For i = Sheets("Index").Index + 1 To WB.Sheets.Count
Sheets(i).Select
CurSheet = ActiveSheet.Name
SubAddress = "='" & CurSheet & "'!$A$2"
DisplayText = CurSheet
Worksheets("Index").Hyperlink.Add Anchor:=Cells(rCount, 1), _
Address:="", SubAddress:=strSubAddress, _
TextToDisplay:=strDisplayText
rCount = rCount + 1
Next i
Sheets("Index").Select
Application.ScreenUpdating = True
End Sub