Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    I have many hyperlinks within a large worksheet (i.e. WS#1) that link to various cells within the same worksheet. When I make a copy of WS#1 and rename it WS#2, all the hyperlinks in WS#2 link back to the cells in WS#1. How can I easily change the hyperlinks in WS#2 en mass so that they will link to the cells within WS#2 and not back to WS#1? Robert

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does something like this code work?

    Steve

    Code:
    Option Explicit
    Sub FixHyperlink()
      Dim wkb As Workbook
      Dim wks As Worksheet
      Dim hlink As Hyperlink
      Dim sOld As String
      Dim sNew As String
      
      Set wkb = ActiveWorkbook
      sOld = "OldPath\OldName.xls"
      sNew = "NewPath\NewName.xls"
      
      For Each wks In wkb.Worksheets
    	For Each hlink In wks.Hyperlinks
      	hlink.Address = Application.WorksheetFunction. _
      	Substitute(hlink.Address, sOld, sNew)
    	Next hlink
      Next wks
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks for the reply, Steve. I know absolutely nothing when it comes to code, so I'm afraid your answer does not help me very much. Isn't there a more simple solution? Robert

  4. #4
    2 Star Lounger
    Join Date
    Jun 2010
    Location
    philippines
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rjstorms View Post
    Thanks for the reply, Steve. I know absolutely nothing when it comes to code, so I'm afraid your answer does not help me very much. Isn't there a more simple solution? Robert
    have you tried find and replace. ctrl + F in the replaced tab then advanced option enter the links and then replaced all. or use mr. storms advise, under macro create a new macro, then paste mr. storms code.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks for the reply, Bong. I tried your suggestion but it does not work. I get a message that says, "MS Excel cannot find any data to replace." Any other suggestions, please? Robert

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you attach a sample workbook without any personal data?

    Thanks

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I think bong was on target.
    I am going to guess at what is trying to be done. See attached workbook.

    The tab WS#1 is how you started. The tab WS#2 is after copying. The tab Sheet3 is what I think you want.
    If so, on tab WS#2 , place cursor in Cell A1, get your Find/Replace to look like it is here ......

    [attachment=89693:find replace hyperlinks.jpg]

    Press button for Replace All.

    Hope this helps.

    Tim
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks for your reply, Tim. Attached is a sample worksheet that will illustrate my problem.
    The hyperlinks are for maneuvering around a very large worksheet, but are close together in the sample worksheet for sake of illustration.

    In the MASTER worksheet, click Edit | Move or Copy Worksheet, and then check the box Create a Copy | OK. Then, rename the new worksheet SORT. Now, you will see that when you click the hyperlinks in the SORT worksheet, they jump to the correct cell but back in the original MASTER worksheet, not in the new SORT worksheet.

    I need a quick and easy way to change all the hyperlinks in the new SORT worksheet so that they will jump to the correct cell in the SORT worksheet, and not back to the original MASTER worksheet. I have too many hyperlinks to do them manually.

    Thanks for your help. Robert
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Go to tools - macro - visual basic editor
    Insert Module
    Then copy the code below and paste it into the window on the right:
    Code:
    Option Explicit
    Sub FixHyperlink()
      Dim hlink As Hyperlink
      Dim sOld As String
      Dim sNew As String
      
      sOld = "Master"
      sNew = "sort"
      
      For Each hlink In ActiveSheet.Hyperlinks
    	hlink.SubAddress = Application.WorksheetFunction. _
    	Substitute(UCase(hlink.SubAddress), UCase(sOld), sNew)
      Next hlink
    End Sub
    File - close and return to miscrosoft excel
    Select the worksheet that you want to change the hyperlinks on
    Tools - macro - macros
    select "FixHyperlink" from the list
    [Run]

    After the macro runs, the hyperlinks should be changed from "Master" to "Sort".

    If/when you want to change to other names:

    Go to tools - macro - visual basic editor
    Goto to Module1
    in the code change the "Master" to the find-name (sOld) and the "Sort" to the replace-name (sNew) and then run the macro again...

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Steve,
    The macro worked like a charm! Thank you very much!
    Robert

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are very welcome

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •