Hi siddiq1212,
Assuming you can have 1 cell within each sheet empty, I chose cell "A1" on each sheet
...and assuming that you table is in range "B1:C10" for demonstration purposes...
You can make a copy of the workbook you have, open the copy, create a new sheet named: Index
After you have created the sheet, you need to go into the VBA editor by pressing Alt+F11
Click on the Index Sheet Module. In the Index Sheet Module, paste in the following code:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim wSheet As Worksheet
Dim l As Long
For Each wSheet In ThisWorkbook.Worksheets
If wSheet.name <> "Index" Then
wSheet.Activate
'************CHANGE RANGE AS REQUIRED**********
Range("B1:C10").name = wSheet.name
End If
Next wSheet
Sheets("Index").Activate
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
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Each time you make a new sheet and rename it, you must click on the Index sheet. Once you click the Index Tab, what it will do is create a new named range based on the sheet name. It will also add a hyperlink to that sheet.
I am assuming that you want to do this for all sheets in the workbook except the Index sheet which is used as a summary of all your sheets which you can hyperlink to.
I have also attached a workbook sample.
Thanks.
Bookmarks