Hyperlink

[Harikrushna]

I am having workbook containing number of worksheets say around 200. in summary sheet ( sheet no. 1 ) , I have list of name of all worksheet having hyperlink to particular sheet. now i want to change hypelink all together . any formula or any VBA code available ?

 

[Harikrushna]

Summary

 

[FormR]

Summary

Try:

Sub CreateTOC()
    '   Code by Zack Baresse
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        Exit Sub
    End If
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    
        Dim WS As Worksheet, _
            ct As Chart, _
            shtName As String, _
            nrow As Long, _
            tmpCount As Long, _
            i As Long, _
            numCharts As Long
        
        nrow = 3
        i = 1
        numCharts = ActiveWorkbook.Charts.Count
        
        On Error GoTo hasSheet
        Sheets("Summary").Activate
        If MsgBox("You already have a Summary page.  Would you like to overwrite it?", _
        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
        Exit Sub

hasSheet:
    Sheets.Add before:=Sheets(1)
    GoTo hasNew

createNew:
    Sheets("Summary").Delete
    GoTo hasSheet

hasNew:
    tmpCount = ActiveWorkbook.Charts.Count
    If tmpCount > 0 Then tmpCount = 1
        ActiveSheet.Name = "Summary"
        
        With Sheets("Summary")
            '.Cells.Interior.ColorIndex = 4
                With .Range("B2")
                    .Value = "Summary"
                    .Font.Bold = True
                    .Font.Name = "Calibri"
                    .Font.Size = "24"
                End With
        End With
        
        For Each WS In ActiveWorkbook.Worksheets
            nrow = nrow + 1
            With WS
                shtName = WS.Name
                With Sheets("Summary")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Hyperlinks.Add _
                        Anchor:=Sheets("Summary").Range("C" & nrow), Address:="#'" & _
                        shtName & "'!A1", TextToDisplay:=shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                End With
            End With
        Next WS
        
        If numCharts <> 0 Then
            For Each ct In ActiveWorkbook.Charts
                nrow = nrow + 1
                shtName = ct.Name
                With Sheets("Summary")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Value = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                End With
            Next ct
        End If
        
        With Sheets("Summary")
            With .Range("B2:G2")
                .MergeCells = True
                .HorizontalAlignment = xlLeft
            End With
        
            With .Range("C:C")
                .EntireColumn.AutoFit
                .Activate
            End With
            .Range("B4").Select
        End With
    
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _
        "Charts are listed after regular " & vbCrLf & _
        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"

End Sub

 

[Harikrushna]

It is creating list by name of sheet. but in my summary sheet name is different. i want to continue that name

 

[FormR]

If new sheets are added how do we determine what your "different" name for them would be?

 

[Harikrushna]

thats true. then can you suggest to change the list without changing hyperlink i.e. if in list hyperlink is with name 123 and i want to make it ABC instead of 123 without changing hyperlink?

 

[FormR]

How do we know that 123 needs to become ABC?

 

[Harikrushna]

i have list of such corresponding conversion. without changing manually individul- by coding or formula can we change as list is too long

 

[FormR]

Where is this list - what sheet, what range of cells.

Where are the corresponding list of values that need to be changed (I understand they are in the sheet called summary) -but what is the range of cells?

Are the values that need changing part of the hyperlink (i.e. the "Friendly name") or are they in cells by themselves?

I'm not going to be on-line for a while now, so will look later (if no-one else jumps in in the meantime) after you have answered these questions.

 

[Harikrushna]

yes values are "Friendly name" cell range are B7:B200

 

[FormR]

i have list of such corresponding conversion.

Where is this list - what sheet, what range of cells.

?