Search This Blog

Monday 30 July 2012

CREATE AN INDEX FOR YOUR WORKBOOK

Paste the below code in the module and run it


Sub create_index()
Dim i As Integer
Dim newsheet
Dim j As Integer

Set newsheet = Worksheets.Add(before:=Sheets(1))
 
   With newsheet
        .Name = "Index"

            With Range("A1:H1")
                 .Merge
                 .Value = "INDEX"
                 .HorizontalAlignment = xlCenter
                 .VerticalAlignment = xlCenter
                 .Font.Bold = True
                 .Font.Color = vbRed
                 .Font.Size = 13
            End With

j = 2
 
   For i = 2 To ThisWorkbook.Sheets.Count
          .Range("A" & j) = Sheets(i).Name
          .Range("A" & j).Select
          .Hyperlinks.Add Anchor:=Selection, _
            Address:="", SubAddress:="" & Sheets(i).Name & "!A1"

j = j + 1
 
   Next

ActiveWindow.DisplayGridlines = False

    End With

End Sub


This code will insert a sheet in workbook named "Index". This sheet will have list of name of all sheets with hyperlink. You can move to any sheet directly by clicking on it's name.

No comments:

Post a Comment