Question Locked

Placing Worksheet Index Results in Columns

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

4 replies

by scarlet manuka

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

by rscott7706

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

This discussion is closed to new replies.