none
Actualización de Hipervinculos en hojas que cambian constantemente RRS feed

  • Question

  • Desarrolle Una macro para Modificar el nombre de Varias hojas SEGÚN EL MES Seleccione Que, sin embargo, al Hacer Eso Se rompe el HIPERVINCULO ya CREADO, ES: Por ello, que me gustaria sable de Como Hacer PUEDE párrafo Complementar esa macro Para Que actualice el HIPERVINCULO

    Friday, June 9, 2017 2:46 AM

All replies

  • Hi Andrés_Miranda,

    I try to translate your description above.

    "Develop a macro to modify the name of several sheets according to the month Select that, however, when doing that the HYPERLINK already created is broken, it is: Therefore, that I would like to know how to do can COMPLETE that macro To update the HYPERLINK"

    you can try to use code below.

    Sub Demo1()
    
        Const OldSheetName As String = "Sheetone"
        Const NewSheetName As String = "Sheetone1"
    
        Dim Ws As Worksheet
        Dim Hlink As Hyperlink
        Dim SplitName() As String
        
        Set Ws = ActiveSheet
        
        For Each Hlink In Ws.Hyperlinks
            With Hlink
                SplitName = Split(.SubAddress, "!")
                If UBound(SplitName) > 0 Then
                    If InStr(1, .SubAddress, OldSheetName, vbTextCompare) Then
                        .SubAddress = "'" & NewSheetName & "'!" & SplitName(1)
                    End If
                End If
            End With
        Next Hlink
    End Sub
    

    first you need to rename the sheet. it depends upon you whether you rename the sheet by user interface or by code.

    then you just need to provide old sheet name and new sheet name in code above and run that code.

    all the link will be updated.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 12, 2017 5:49 AM
    Moderator