[SOLVED] External Links Still Shows Even When The Data Is Value Pasted

External Links Still Shows Even When The Data Is Value Pasted

I have a macro-enabled workbook stored in OneDrive. Within this workbook, I've recorded a macro designed to generate a new workbook. This newly created workbook is a copy of one of the sheets from the original workbook, and it is saved to the desktop. To ensure data integrity, I've attempted to break the links of the formulas used within the workbook. However, despite my efforts, the external links persist.

I have tried to resolve the issue by employing a method in which I copy and paste values for most of the formulas, even after this procedure, the external links persist, when I click on Data tab Workbook links, although there is not a single formula in the sheet which refers to other external workbook, however there is one hyperlink formula which is referencing within the workbook sheet cells and not the external one.

My question is whether there is a method to eliminate these external links automatically, rather than resorting to manual removal. Here is the macro I've recorded for reference.

Sub Macro4()
    Sheets("Email_To_Managers").Select
    Sheets("Email_To_Managers").Copy
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                           :=False, Transpose:=False
    Range("G4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                           :=False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False
    Selection.Locked = True
    Selection.FormulaHidden = True
    Range("F155").Select
    Selection.End(xlUp).Select
    Range("F4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Locked = False
    Selection.FormulaHidden = True
    ActiveSheet.Protect "noway19_97", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="noway19_97"
    ActiveWorkbook.SaveAs Filename:="C:\Users\WilliamTschetter\Desktop\NEW_IN_DEMANDS.xlsx", _
                          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Solution

Your macro has redundant code, Here is edited version of your macro that break the external links.

Sub CreateCopyAndBreakLinks()

    Dim newWb As Workbook
    Dim ws As Worksheet
    
    ' Copy the worksheet to a new workbook
    ThisWorkbook.Sheets("Email_To_Managers").Copy
    Set newWb = ActiveWorkbook
    Set ws = newWb.Sheets(1)
    
    ' Break external links
    Dim link As Variant
    For Each link In newWb.LinkSources(Type:=xlLinkTypeExcelLinks)
        newWb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
    Next link
       
    ' Protect sheet and workbook
    ws.Protect Password:="noway19_97", DrawingObjects:=True, Contents:=True, Scenarios:=True
    newWb.Protect Structure:=True, Windows:=False, Password:="noway19_97"
    
    ' Save the new workbook
    newWb.SaveAs Filename:="C:\Users\WilliamTschetter\Desktop\NEW_IN_DEMANDS.xlsx", _
                 FileFormat:=xlOpenXMLWorkbook
    newWb.Close SaveChanges:=False
    
End Sub