locked
Breaking links to a certain worksheet RRS feed

  • Question

  • I have a worksheet (ex., "Sheet1") which contains links of two types:

    1) formulas with links to the same worksheet;

    2) formulas with links to another worksheet (ex., "TMP_Sheet") in the same workbook.

    I am looking for a macro which could break the links to "TMP_Sheet" saving the values in the cells and preserving formulas with links to "Sheet1".

    Is it possible?

    Thank you!

    Tuesday, February 9, 2016 2:30 PM

Answers

  • Hi M,

    >>I am looking for a macro which could break the links to "TMP_Sheet" saving the values in the cells and preserving formulas with links to "Sheet1".

    Do you mean you want to convert formula to value when formula contains “TMP_Sheet”? If so, I suggest you try the code below, and modify it for your own requirement:

    Sub test1()
    Debug.Print ActiveCell.Formula
    Dim str As String
     str = ActiveCell.Formula
    
    If InStr(str, "TMP_Sheet") > 0 Then
        ActiveCell.Formula = ActiveCell.Value
    End If
    End Sub

    Best Regards,

    Edward

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Мишель Wednesday, February 10, 2016 6:08 AM
    Wednesday, February 10, 2016 3:07 AM

All replies

  • I'm sorry I cannot understand your want.
    Would you share your Excel file via cloud storage such as OneDrive, Dropbox, etc.
    Wednesday, February 10, 2016 3:00 AM
  • Hi M,

    >>I am looking for a macro which could break the links to "TMP_Sheet" saving the values in the cells and preserving formulas with links to "Sheet1".

    Do you mean you want to convert formula to value when formula contains “TMP_Sheet”? If so, I suggest you try the code below, and modify it for your own requirement:

    Sub test1()
    Debug.Print ActiveCell.Formula
    Dim str As String
     str = ActiveCell.Formula
    
    If InStr(str, "TMP_Sheet") > 0 Then
        ActiveCell.Formula = ActiveCell.Value
    End If
    End Sub

    Best Regards,

    Edward

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Мишель Wednesday, February 10, 2016 6:08 AM
    Wednesday, February 10, 2016 3:07 AM
  • Edward, thank you very much!
    Wednesday, February 10, 2016 6:08 AM