I have a following code that list and link all my worksheets in one long list. Now I come to the fact that this linked list is too long to me and I wonder if it's possible to split long linked lists into columns. I have seen separate codes that just copy out the values and split list into columns but in my case I want these cells linked too. So I am stuck and no idea how to implement code in this macro. Below is my macro.
Code
Private Sub Worksheet_Activate()
Dim xSheet As Worksheet, x As Long, xName As Name
Application.ScreenUpdating = False
Me.Move Before:=Sheets(1)
x = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "INDEX"
.Cells(1, 1).Font.Bold = 1
.Cells(1, 1).Borders(xlEdgeBottom).LineStyle = xlDouble
End With
For Each xSheet In Worksheets
If xSheet.Name <> Me.Name Then
x = x + 1
With xSheet
' Uncomment for the first use and launch. The comment again.
' .Rows("1:1").Insert Shift:=xlUp
' .Rows("1:1").Clear
.Range("A1").Name = "_" & .Name
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="INDEX", TextToDisplay:="Back to Index"
.Range("A1").Font.ColorIndex = xlAutomatic
.Range("A1").Font.Bold = 1
Me.Hyperlinks.Add Anchor:=Me.Cells(x, 1), Address:="", SubAddress:="_" & .Name, TextToDisplay:=.Name
Me.Cells(x, 1).Font.Underline = xlUnderlineStyleNone
Me.Cells(x, 1).Font.ColorIndex = xlAutomatic
Me.Cells(x, 1).Font.Bold = 1
End With
End If
' Me.Columns(1).AutoFit
Next xSheet
Application.ScreenUpdating = True
End Sub
Display More
How to do that?