VBA Copy Sheets and Break All External Links

[cheongmarcus]

Hi, I am new to VBA and would really appreciate if anyone could help me with this. Currently, I am trying to only copy certain sheets out of a workbook and save it in new workbook. However, in my new workbook with those selected worksheets, when i go to Edit Links, there is link to my previous workbook. Is it possible to break this link? I would like to incorporate this in the VBA code, so then I will not need to open up this new workbook and manually go and break link.

I have this code to copy certain worksheets:

Sub Seperate_Sheets()

Dim Path1 As String
Path1 = ActiveWorkbook.Path & "\" & "Current Budget "

Sheets(Array("Expenses", "Revenues", "Previous Year Expenses", "Previous Year Revenue")).Copy
ActiveWorkbook.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

End Sub

However, I will want to break all links in this new workbook ("Current Budget"). What code and how can it be inserted inside this current code I am using?

Thank you! Any help will be very much appreciated!

 

[MrLearning]

Hi welcome to forum

try following update to your code

Sub Seperate_Sheets()
    Dim Path1 As String
    Dim wb As Workbook
   
    Path1 = ActiveWorkbook.Path & "\" & "Current Budget"
   
    Sheets(Array("Expenses", "Revenues", "Previous Year Expenses", "Previous Year Revenue")).Copy
   
    Set wb = ActiveWorkbook
   
    BreakAllLinks wb
   
    wb.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    wb.Close False
   
End Sub

Following sub should be placed in a STANDARD module

Sub BreakAllLinks(ByVal wb As Object)
    Dim Link As Variant, LinkType As Variant
   
    For Each LinkType In Array(xlLinkTypeExcelLinks, xlOLELinks, xlPublishers, xlSubscribers)
        If Not IsEmpty(wb.LinkSources(Type:=LinkType)) Then
            For Each Link In wb.LinkSources(Type:=LinkType)
                wb.BreakLink Name:=Link, Type:=LinkType
            Next Link
        End If
    Next LinkType
    wb.UpdateLinks = xlUpdateLinksNever
End Sub

Dave

Hi Dave,

I am new to the forum, and I found your post about break link what is I need for my work. For this line:
"Sub BreakAllLinks(ByVal wb As Object) "
Why you pass the wb by value, not by reference? Is that the reason why it does not clear all the link? (Since this pass by value, it may create a copy of wb link instead of actual object wb)

I know this post is old, but if you answer this that would be great.

Thanks