Excel 2010, Windows 7, working on Excel 2003 .xls in compatibility mode
I have written macros that copy the data from 12 respective visible worksheets to corresponding hidden worksheets. I also have macros that copy the hidden worksheets back again to the visible worksheets. The pages are heavily formatted with conditional formatting, drop-down lists, and check boxes.
My problem is this:
The more I manipulate the pages with my macros (ie. copy the 12 sheets of data to the corresponding sheets and reset back again), the larger my file gets. This makes sense the first time I copy the data over, as 12 invisible and empty sheets become populated with data they didn't previously have. But any subsequent reset or copy only serves to paste over existing data that takes up the same space. It doesn't seem like the file size should continue to balloon at this point, though it does.
When I try to close the application, I get an error: "Picture is too large and will be truncated."
It pops up several times, and then the save and close operation take an increasing amount of time as the file gets larger.
Solutions I have tried:
I added the VBA statement "Application.CutCopyMode = False" to the end of each copy/paste operation in order to clear the contents of the clipboard, thinking that was my problem. I read that that code is no longer functional in 2010, though, and it turns out to not solve my issue of ballooning file size and error messages. That code is supposed to clear the Office Clipboard, but I understand there is also a Windows Clipboard. Could one of these two clipboards be the problem (as I am copying and pasting fair amounts of data)? How do I ensure that repeated use of my macros does not balloon the size of my spreadsheet?
I am in desperate need of help understanding this problem and determining a solution. Thank you very much, in advance. If it would help to see any of my code, please let me know.
I have written macros that copy the data from 12 respective visible worksheets to corresponding hidden worksheets. I also have macros that copy the hidden worksheets back again to the visible worksheets. The pages are heavily formatted with conditional formatting, drop-down lists, and check boxes.
My problem is this:
The more I manipulate the pages with my macros (ie. copy the 12 sheets of data to the corresponding sheets and reset back again), the larger my file gets. This makes sense the first time I copy the data over, as 12 invisible and empty sheets become populated with data they didn't previously have. But any subsequent reset or copy only serves to paste over existing data that takes up the same space. It doesn't seem like the file size should continue to balloon at this point, though it does.
When I try to close the application, I get an error: "Picture is too large and will be truncated."
It pops up several times, and then the save and close operation take an increasing amount of time as the file gets larger.
Solutions I have tried:
I added the VBA statement "Application.CutCopyMode = False" to the end of each copy/paste operation in order to clear the contents of the clipboard, thinking that was my problem. I read that that code is no longer functional in 2010, though, and it turns out to not solve my issue of ballooning file size and error messages. That code is supposed to clear the Office Clipboard, but I understand there is also a Windows Clipboard. Could one of these two clipboards be the problem (as I am copying and pasting fair amounts of data)? How do I ensure that repeated use of my macros does not balloon the size of my spreadsheet?
I am in desperate need of help understanding this problem and determining a solution. Thank you very much, in advance. If it would help to see any of my code, please let me know.
