Breaking links when using Worksheet.Copy method

Hello,

I have a big workbook with formulas on some worksheets that refer to other worksheets.

Now I want to export (or copy) one single worksheet, to a new workbook using a VB macro. But I need to break the links in the new workbook, as I don't want its data to get updated when I change something in the "mother" work book.

I used the Worksheet.Copy method. In the code below, "Report" is the sheet I want to export (or copy):

Sub Export()
Worksheets("Report").Copy
End Sub

My problem is that the new workbook keeps links to the mother workbook. It even keeps the macro. This is not what I want, as the exported single sheet is simply a report.

Could you please help?

Many thanks in advance.

Hi Fakhfour,

I had a similar situation where I had to export multiple sheets from the same workbook. I altered the code that worked for me so you can drop it into your VBA module. This won't save it anywhere, but simply open it in a new workbook and break all links. Also, the macro doesn't carry over.

I also attached a simple test file I ran with the code and it worked for me.

Sub Export()


'Copy and break links of "Report"
Sheets(Array("Report")).Copy
    varLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If IsArray(varLinks) Then
        For i = LBound(varLinks) To UBound(varLinks)
            ActiveWorkbook.BreakLink Name:=varLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
    
End Sub

Hope it helps.
Zarley

Originally Posted by Zarley

...

I had a similar situation ...
Zarley

Thank you very much. It almost worked perfectly. I say almost, because on the exported file, I still see the button that I had created on the original for export.
Thank you.

Hi fakhfour,

You can insert a 'select and delete' code into the VBA macro once the new sheet is created. In the below code, I used "Button 1" as the name of the button, if yours has a different name, simply change it in the code.

'Remove button
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete

The final completed code should look something like the following:

Sub Export()


'Copy and break links of "Report"
Sheets(Array("Report")).Copy
    varLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If IsArray(varLinks) Then
        For i = LBound(varLinks) To UBound(varLinks)
            ActiveWorkbook.BreakLink Name:=varLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
'Remove button
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
End Sub

Zarley

You really rock!!!! Thanks...
I have another challenge for you: if I have macros in the "Report" sheet, your code will return #NAME? error, as it looks for the functions in the new file. Do you have a solution for this?

Could you upload a sample of the source file you're generating the report from? It will help make it easier to determine the cause and possible solution.

Thanks,
Zarley

Originally Posted by Zarley

Could you upload a sample of the source file you're generating the report from? It will help make it easier to determine the cause and possible solution.

Thanks,
Zarley

Dear Zarley,
I should have said that I have functions not macros. Anyway, here is the excel file with which I still have export problems.

You really rock!!!! Thanks...
I have another challenge for you: if I have macros in the "Report" sheet, your code will return #NAME? error, as it looks for the functions in the new file. Do you have a solution for this?

Hi Fakhfour,

The user defined function changes things a little. From what I know, you can't copy the sheet to a new workbook without generating that "#NAME" issue as the new worksheet does not have that user defined function. So, to work around this, I created a third sheet in your workbook labeled "Export". This allowed me to do a copy/paste special values from your 'Report' sheet and do the exporting from the new 'Export' sheet in order to avoid having the user defined function.

I assume the file we're working on here is a sample file, so I also included code to copy not only that data from 'Report', but the formats as well. Just in case your working file had formats you wanted to retain on your final exported report.

The code is below, and I've uploaded the updated file here. Let me know if this works for you!

Sub Export()

' Copy values from Report and paste into Export sheet.
Application.ScreenUpdating = False
    Sheets("Report").Select
    Cells.Select
    Selection.Copy
    Sheets("Export").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("Report").Select
'Export Report Data
    Sheets(Array("Export")).Copy
    varLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If IsArray(varLinks) Then
        For i = LBound(varLinks) To UBound(varLinks)
            ActiveWorkbook.BreakLink Name:=varLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
    ActiveSheet.Name = "Report"
Application.ScreenUpdating = True
End Sub

Zarley

Originally Posted by Zarley

Hi Fakhfour,
...

Dear Zarley,
Thank you again. Your example works fine. I'm going to try it on the real workbook (it's 15MB; I couldn't upload it). You really rule.