Results 1 to 4 of 4

Thread: File Size Ballooning w/ Repeated use of Copy/Paste Macro

  1. #1

    File Size Ballooning w/ Repeated use of Copy/Paste Macro

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    New Zealand
    Maybe your contidional formattig is getting duplicated each time you copy and paste, to the point that you have multiple duplicate conditions covering the same range?

  3. #3
    That sounds incredibly likely. I can say at this point that the problem does not occur when working in Excel 2003, only when working in 2010 in compatibility mode. Can you suggest a solution or a way to find out if this is the case? How can I delete formatting when I'm clearing the contents of the hidden sheets prior to re-copying into them? Or should I not copy formatting at all? My hidden sheets do not require it as they will not be seen. Below is my code for copying over the sheets. Can you help me code it so that formats don't duplicate? Thanks so much for your input so far. You are the first person to suggest something that's really made sense within the context of my problem.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Code.PNG 
Views:	38 
Size:	21.1 KB 
ID:	1459  

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    New Zealand
    Ahh, I see you're copying whole sheets, rather than specific ranges. I take it this code ain't the speediest? ;-)

    Better to copy only the values you need to the specific place you need them, then just copy the values back.

    So instead of this bloat-inducing bottleneck:
    Sheets("Prices").Cells.Copy Sheets("Scen1_Prices").Cells(1,1)
    ...use this:
    Range("Destination").value = Range("Source").value

    ...where Destination and Source are worksheet ranges.

    More info at:
    ...or just do a google on efficient ways to copy etc

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts