Copying Sheets without Links to the Original Sheet

[MikeDC85]

Hello Excel Gurus,
I have a Workbook at my office that produces TV Edit Reports based on what the employee enters. Upon clicking a save button, the sheet saves a certain range of cells in that sheet (with their values & formulas) to a brand new shiny workbook, saves it and closes it. Another department will then open these exported sheets for future reference. However, I'm in a bit of a muddle as Excel isn't behaving as I want - upon opening the saved exported sheets I get links to the original sheet in my formulas...
eg."" =IF(L12="","",'\\dne\collaborate\MTP&O\Post Production Services .... tcupdate.xla'!TCsum(L12:L18)) ""
This creates all kinds of weird pop ups when my colleagues open the file. I need values to be retained and formulas to stay localised to the current sheet. I've spent a few days now going through the inter-web to find a solution but I'm stuck. When I create the Workbook copy I run this...

    With ActiveWorkbook.Sheets(SafeShtName).Range("A1:Q65")
        .Copy
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormulas
        .Range("A1").Select
    End With

...expecting the values to be pasted in, then have the formulas pasted over the top to apply those formulas to the necessary cells. But still I get links. I've tried, copying and pasting the whole sheet, I've tried a find & replace but couldn't get to search the formulas.
Anyone have any ideas how to achieve this?
MikeDC

 

[mumps]

I found this macro written by Luke M (How to export a worksheet without links to a new work book using VBA) You could give it a try.

Sub BreakAllLinks() 
    Dim aLinks As Variant 
    Dim i As Long 
     'Creates array of all links in workbook
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) 
    If Not IsEmpty(aLinks) Then 
        For i = 1 To UBound(aLinks) 
            ActiveWorkbook.BreakLink aLinks(i), xlLinkTypeExcelLinks 
        Next i 
    End If 
End Sub

 

[MikeDC85]

Thanks Mumps,

Sadly no luck. I think it's because when I'm copying to a new Workbook, the 'links'! don't exist at that moment, only upon re-opening them when I'm prompted to update/don't update links do they appear and everything goes crazy. Is there anyway to save without links?

Thanks

 

[mumps]

Perhaps you could try by placing the macro into a workbook_open event so that it is run automatically when you open the workbook.

 

[MikeDC85]

Hi,

I thought I'd share my workaround if anyone else has issues. After exploring the links which were referring back to very old versions of this WorkBook, I exported all my sheets and modules to a new Workbook and I'm no longer being prompted about it thankfully. I do get an "enable Content" button that appears but as long as I don't click it everything works great!

Thanks for all your help you lovely Excel People!