MrExcel Publishing
Your One Stop for Excel Tips & Solutions

clearing clipboard/excel buffer

Posted by cori on May 06, 2001 11:22 PM

i am copying and pasting large amounts of data from one workbook to other workbooks (a giant loop) (only selected worksheets are needed)

my problem is that the excel buffer gets filled up and an error is generated, is there any way to clear the buffer so i can continue with my processing? (one way i assume is by closing the applications but it's not that good an option)

thanx c :)

Posted by Dave Hawley on May 06, 2001 11:28 PM

Hi cori

If you are using Excel 97 and the method you are using is copying the Sheet object then this is a known problem as the CodeName for the sheet adds a 1 each time so after 10 copies you end up with a CodeName like Sheet1111111111 this number eventually become to large. This problem seems to have been fixed in Excel 2000 (at least they say so and I haven't encountered it).

Rather than copying the sheet Object use the Cells.Copy method and paste in to a new sheet.


OzGrid Business Applications