Performance tanks when sheet is copy/pasted to new file

[Fred_Canney]

I have a fairly complex worksheet with graphs and named ranges, but the sheet is fully self-contained (all named ranges, graphs, formulas reference the sheet itself and no other sheets).  The sheet calculates really quickly and doesn't seem to have any performance issues in the original template.

But when I right-click on the sheet name and copy the sheet to a new workbook, the performance of the sheet deteriorates significantly.  There is a ~10 second delay for any calculation to kick off where the sheet appears to be "thinking" before it decides to calculate.  This doesn't happen on the original sheet, but happens only in the new workbook sheet. 

I've tried a few things and noticed a few things, but can't figure out what is happening - any help would be appreciated.  Here's what we've noticed:

• When we copy/paste the sheet, an external link is created back to the original workbook.  I've checked all of the formulas (search for ".xl") and found nothing.  I've checked the objects, all of the graph ranges, the name ranges and everywhere else that I can come up with and haven't been able to find the source of the external link.  When we click "break link" it doesn't seem to do anything.  And whether we click break-link or leave it alone, when we save the file and re-open it, the external link is gone.  Not sure if this is related to the problem or not.
• I tried the calculation timers here (https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcul...) and the calculation time definitely increases significantly (10x) in the new workbook, but all of the formulas are exactly the same (as far as I can tell)

Any ideas for how to diagnose the problem, identify the slowness and improve performance would be appreciated.  Thanks!

[#2]

@Fred_Canney 

Hard to say from your description. I do wonder why and how you're copying (in effect re-producing it) in its entirety via that method.

So I'm going to begin there with what might be a better way to create that copy: Save the original as a template, and then just open a new copy from the template, give it a new name (which I presume you're doing with your own resulting copy).

If you've not tried that before, give it a try and report back. Here's a useful reference.

[#3]

Thanks @mathetes.  Through a ton of trial/error today, I found that it is about 10 conditional formatting rules that are being applied to ~1000 rows x ~50 columns of data.  This formatting works fine in the original template.  After we right-click on the worksheet tab name and copy it into a new file, the conditional formatting starts to cause the slowness.

If I delete the conditional formatting from those cells completely - the copy of the worksheet performs just fine.  And in the original template, the worksheet performs just fine with the conditional formatting.  But something in the making of the copy of the worksheet is what is causing the file performance to hang.

Also, if I copy the worksheet into a new workbook, delete the conditional formatting, then recreate the conditional formatting manually, it works just fine.  So there is some bug in the worksheet copy that is causing the problem.  I'm going to try a VBA to rebuild the conditional formatting, but not sure if that will work. 

Any ideas of what this might be?

[#4]

@Fred_Canney 

Any ideas of what this might be?

No, I don't understand the inner/behind-the-scenes workings of Excel.

But I also don't see you talking of changing your process so you save the original as a template and then open it and do whatever it is you do with it as a new sheet.

I'd like to have you at least confirm that you've tried that route--Original File to Template to New File--conditional formatting and all formulas intact. I don't KNOW that it will resolve the issue, but would like to know that you've tried it instead of copying. [As noted, I don't pretend to understand the behind-the-scenes working of Excel, but it is a different process, and was created for a purpose.]

And if it's not all confidential or proprietary, are you at liberty to describe in general terms what the spreadsheet is for, what purpose it serves, and why so much conditional formatting. There may be other ways to highlight or extract the items you're highlighting with colors and other formats. Said another way, you may be making this a more complicated spreadsheet than is necessary. Using 10 rules to highlight 50,000 cells in different ways verges on the excessive; since I don't know the application, I'll grant that it may be necessary, but I'd like to be convinced, especially since it appears to slow things down.

[#5]

Thanks @mathetes - unfortunately, the template approach isn't going to work for us on this one due to how the sheets are used and the flexibility that is needed to be able to merge these templates into other files that are pre-existing.

But on the positive side, we were able to narrow down to something that is happening to the conditional formatting when the sheet is copy/pasted.  We're going to attempt using VBA to remove and restore the conditional formatting to see if this fixes the issue.