This is a cool Excel macro, but I have a Workbook with 248 sheets.
Can I tweak this to put the results in columns?
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
Ron
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (1)
0
Votes
Easy tweak
Change the declaration of NumCols to be however many columns you would like. The index links are placed from left to right for the specified number of columns and for as many rows as are required.
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim I As Long, RowNum As Long, ColNum As Long
Const NumCols As Integer = 5
With Me
For I = 1 To NumCols
.Columns(I).ClearContents
Next I
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
I = 0
For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
I = I + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
RowNum = (I - 1) \ NumCols + 1
ColNum = (I - 1) Mod NumCols + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(RowNum + 1, ColNum), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim I As Long, RowNum As Long, ColNum As Long
Const NumCols As Integer = 5
With Me
For I = 1 To NumCols
.Columns(I).ClearContents
Next I
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
I = 0
For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
I = I + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
RowNum = (I - 1) \ NumCols + 1
ColNum = (I - 1) Mod NumCols + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(RowNum + 1, ColNum), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
12th Mar
Replies
My spreadsheet doesn't like it. here is the message. I have tried to debug, but so far unsuccessful.
Here is a screen dump: http://rsconcepts.com/images/Image6.png
Here is a screen dump: http://rsconcepts.com/images/Image6.png
rscott7706@...
12th Mar
I got it there was a small code change needed.
For anyone that needs it, here is the code that works:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim I As Long, RowNum As Long, ColNum As Long
Const NumCols As Integer = 5
With Me
For I = 1 To NumCols
.Columns(I).ClearContents
Next I
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
I = 0
For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
I = I + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
RowNum = (I - 1) \ NumCols + 1
ColNum = (I - 1) Mod NumCols + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(RowNum + 1, ColNum), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
For anyone that needs it, here is the code that works:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim I As Long, RowNum As Long, ColNum As Long
Const NumCols As Integer = 5
With Me
For I = 1 To NumCols
.Columns(I).ClearContents
Next I
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
I = 0
For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
I = I + 1
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
RowNum = (I - 1) \ NumCols + 1
ColNum = (I - 1) Mod NumCols + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(RowNum + 1, ColNum), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
rscott7706@...
13th Mar
