Search
  • Admin

Excel Workbook Index Page Creator - Upskill Technologies

With excel spreadsheets being used everywhere it’s a common scenario that you will have too many sheets in a workbook and it gets difficult to navigate between sheets. This code can help you create a index page with table of contents linking to all sheets in a workbook and every sheet will have a link back to the index sheet.

Steps to use this code: Go to VBA editor and add a new module. Add the below code to the new module. Go back to the excel workbook where you want the table of contents to be created. Now hit Alt+F8 and run the macro “IndexCreator”. Voila! You will have the index page created on the first page with the table of contents linking to each sheet.

Download the file in case you have any difficulty from the Downloads page.

Sub indexcreator()

Dim st As Worksheet, i As Integer

Sheets.Add before:=Sheets(1)

On Error GoTo k

ActiveSheet.Name = "Index"

Cells(3, 2).Value = "Content"

Range("b3:c3").MergeCells = True

For i = 1 To Sheets.Count

Cells(i + 3, 3).Value = Sheets(i).Name

Cells(i + 3, 2).Value = i

Sheets(i).Select

If ActiveSheet.Name <> "Index" Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), Address:="", SubAddress:="Index!A1", TextToDisplay:="Index"

Sheets("Index").Select

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 3, 3), Address:="", SubAddress:="'" & Cells(i + 3, 3) & "'!A1"

Next i

Range("b3").CurrentRegion.Borders.LineStyle = xlContinuous

Range("b3").CurrentRegion.Borders.Weight = xlThin

Columns("b:C").EntireColumn.AutoFit

ActiveWindow.DisplayGridlines = False

Exit Sub

k:

MsgBox "Please remove Index sheet and run the program again"

End Sub

Tags: Advance Excel Training Hyderabad, VBA Macros Hyderabad, Ms Access Training Hyderabad, Corporate Training Hyderabad

#vbamacroshyderabad #advanceexcelhyderabad #msaccess

17 views

Recent Posts

See All

TEST YOUR EXCEL SKILLS IN EXCEL

So you want to know how good you are in excel then make sure you download our test now. Its not a multiple choice question style test but an actual test in excel with excel data. Download it and try t