VBA Code to Break Links

[mhoncho]

I have written code that takes a workbook and copies each tab of the source file into a new individual workbook, saves it as a new file, closes the workbook and then proceeds to the next tab. The last thing I'm trying to do is add code to break all links to the original file in the new workbooks once they are created. Below is the current code and I'm looking for help to insert a string to perform the break link function before saving and closing the new workbook. Note that I'm not trying to paste values, as I want to retain the mathematical formulas, just not the links to the original workbook. Below is the current code that I'm using, would appreciate a suggestion and what and where to insert the break link code.

Sheets("LASTNAME").Select
Sheets("LASTNAME").Copy
ChDir "C:\Users\FIRST.LAST\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\FIRST.LAST\Desktop\LASTNAME.xlsx", FileFormat:=xlOpenXMLWorkbook _
, CreateBackup:=False
ActiveWindow.Close
Windows("ORIGINAL SOURCE FILE.xlsm").Activate

 

[MikeVol]

Hello. Try next code:

    Dim i           As Long
    Dim nmName      As Name

    With Sheets("LASTNAME")
        .Copy
    End With

    ChDir "C:\Users\FIRST.LAST\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\FIRST.LAST\Desktop\LASTNAME.xlsx", FileFormat:=xlOpenXMLWorkbook _
            , CreateBackup:=False

    ' Delete All Link to another Workbook
    Dim WorkbookLinks As Variant
    WorkbookLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(WorkbookLinks) Then

        For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
            ActiveWorkbook.BreakLink _
                    Name:=WorkbookLinks(i), _
                    Type:=xlLinkTypeExcelLinks
        Next i

    End If

    ' Delete All Name
    For Each nmName In ActiveWorkbook.Names

        With nmName

            If nmName.Visible Then

                If Not CStr(.Name) Like "*!_FilterDatabase" Then

                    If Not CStr(.Name) Like "*!Print_Area" Then
                        .Delete
                    End If

                End If

            End If

        End With

    Next

    ActiveWindow.Close
    Windows("ORIGINAL SOURCE FILE.xlsm").Activate

 

[mhoncho]

Thank you! Got it to work with the above.

 

[MikeVol]

You're very welcome!