Question

Locked

Placing Worksheet Index Results in Columns

By rscott7706 ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Easy tweak

by Scarlet Manuka In reply to Placing Worksheet Index R ...

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

Collapse -

Reponse To Answer

by rscott7706 In reply to Easy tweak

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

Collapse -

Reponse To Answer

by rscott7706 In reply to Easy tweak

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

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums