Forum Discussion
Performance tanks when sheet is copy/pasted to new file
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 c...
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.
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?
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.
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.
