Internal Link Problem

    Is the following possible?:


    In a sheet named "Index" there are links to all the other sheets in the same file, except to the sheets who start with the underscore ('_').
    I don't want to maintain all those links manually, is there a way to automate this proces?

    Hi Jethro,


    You look to be determined to get your entire application developed here, but don't mind i like working like this :)


    Its really good that you are coming up with interesting questions one after the another.


    I have taken the sheet from your previous post, http://www.ozgrid.com/forum/viewthread.php?tid=1326
    so that you will not loose that work. Also, I have proactively added two additional columns in the index apart from Sr. and Sheet Name
    1. Description : Currently linked with cell A1 on each sheet
    2. Count of records : Currently linked with Cell H1 on respective (which is formatted as ;;; so not visible) which gives the count


    I have also amended the Overall Sheet Copy formula to take care of this Index page, other wise, it will messup that!!!


    I have also taken the liberty to copy the image you use for Avatar on this site and pasted it on index, and linked the macro to that image, just for fun :D


    Here is the code for Index,


    ===========================================
    Sub create_index()
    'By Yogendra Joshi
    Dim sht As Worksheet
    Dim lnk, lnk_show As String
    Dim cnt As Integer
    'Clear Existing Data


    Application.ScreenUpdating = False
    Sheets("Index").Range("A9").Select
    If (ActiveCell.SpecialCells(xlLastCell).Row) > 8 Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Clear
    End If


    'Checking for Worksheets and copying data
    For Each sht In Worksheets
    If Left(sht.Name, 1) <> "_" And sht.Name <> "Index" Then
    With sht
    lnk = "'" & .Name & "'!A1"
    Debug.Print lnk
    lnk_show = .Name
    Sheets("Index").Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveCell.Value = (Selection.Row) - 8
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = .Name
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
    Address:="", SubAddress:=lnk
    ActiveCell.Offset(0, 1).Value = .Range("A1").Value ' add your own cell if needed
    ActiveCell.Offset(0, 2).Value = .Range("H1").Value ' added a hidden cell for count
    End With
    End If
    Next


    Application.ScreenUpdating = True
    End Sub


    ===========================================


    Hope this helps...


    Please do keep coming with more such brellient ideas, someone here will surely put it to reality. :cheers:

    Thanks: ~Yogendra

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login