Scribd Logo
Upload

Welcome to Scribd!

  • 27 views

    Write Sheet Names in "Index" Worksheet

    Uploaded by

    franklaer-2
    This VBA macro writes sheet names in an "Index" worksheet. It loops through each worksheet in the active workbook, assigns a hyperlink to the first cell with an anchor back to the index, and adds a hyperlink to the index with the sheet name. It disables screen updating for performance and enables it at the end.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Write Sheet Names in "Index" Worksheet

    Uploaded by

    franklaer-2
    27 views2 pages
    This VBA macro writes sheet names in an "Index" worksheet. It loops through each worksheet in the active workbook, assigns a hyperlink to the first cell with an anchor back to the index, and adds a hyperlink to the index with the sheet name. It disables screen updating for performance and enables it at the end.

    Original Description:

    code

    Original Title

    Login

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This VBA macro writes sheet names in an "Index" worksheet. It loops through each worksheet in the active workbook, assigns a hyperlink to the first cell with an anchor back to the index, and adds a hyperlink to the index with the sheet name. It disables screen updating for performance and enables it at the end.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    27 views2 pages

    Write Sheet Names in "Index" Worksheet

    Uploaded by

    franklaer-2
    This VBA macro writes sheet names in an "Index" worksheet. It loops through each worksheet in the active workbook, assigns a hyperlink to the first cell with an anchor back to the index, and adds a hyperlink to the index with the sheet name. It disables screen updating for performance and enables it at the end.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 2

    ‘Write sheet names in “Index” worksheet

    Private Sub Worksheet_Activate()

    Dim worksheet As Worksheet


    Dim xRow As Integer
    Dim calcState As Long
    Dim scrUpdateState As Long
    Application.ScreenUpdating = False
    xRow = 2
    With Me
    .Columns(1).ClearContents
    .Cells(2, 1) = "INDEX"
    .Cells(2, 1).Name = "Index"
    End With
    For Each worksheet In Application.Worksheets
    If worksheet.Name <> Me.Name Then
    xRow = xRow + 1
    With worksheet
    .Range("A1").Name = "Start_" & worksheet.Index
    .Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With
    Me.Hyperlinks.Add anchor:=Me.Cells(xRow, 1), Address:="", _
    SubAddress:="Start_" & worksheet.Index, TextToDisplay:=worksheet.Name
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    ‘Write sheet names in “Index” worksheet

    Private Sub Worksheet_Activate()


    'Updateby20150305
    Dim xSheet As worksheet
    Dim xRow As Integer
    Dim calcState As Long
    Dim scrUpdateState As Long
    Application.ScreenUpdating = False
    xRow = 2
    With Me
    .Columns(1).ClearContents
    .Cells(2, 1) = "INDEX"
    .Cells(2, 1).Name = "Index"
    End With
    For Each xSheet In Application.Worksheets
    If xSheet.Name <> Me.Name Then
    xRow = xRow + 1
    With xSheet
    .Range("A1").Name = "Start_" & xSheet.Index
    .Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With
    Me.Hyperlinks.Add anchor:=Me.Cells(xRow, 1), Address:="", _
    SubAddress:="Start_" & xSheet.Index, TextToDisplay:=xSheet.Name
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    You might also like