Breaking links when using Worksheet.Copy method

[fakhfour]

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.

[TMS]

If that's the code, you just end up with a new workbook, unnamed, unsaved, with a copy of the worksheet.

I would suggest that you select all the data in the copied worksheet and Paste Special | Values. This will convert everything, replacing formulas with their values and losing the links. To lose the macro(s), you'd need to save the new workbook as a non-macro-enabled workbook with a .xlsx file extension.


Regards, TMS

[Zarley]

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.

Please Login or Register  to view this content.

Hope it helps.
Zarley

[TMS]

Neat. Never done that. need to remember this

Also, the macro doesn't carry over.

I suspect there is event code in the worksheet and this WILL get carried across.


Regards, TMS

[fakhfour]

...

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.

[Zarley]

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.

Please Login or Register  to view this content.

The final completed code should look something like the following:

Please Login or Register  to view this content.

Zarley

[fakhfour]

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?

[fakhfour]

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?

[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

[fakhfour]

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.

[Zarley]

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!

Please Login or Register  to view this content.

Zarley

[fakhfour]

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.