MrExcel.com - Your One Stop for Excel Tips & Solutions

I am using Copy and Paste and Excel is making my file huge

Posted by adam on April 23, 2001 7:53 AM

I have a workbook that has macros that copy and paste many ranges. I just recently saved it and even though the file should be about the same size. the file went from 2 megs to over 14. can anyone explain why this happens and how to workaround this problem. I am assumming it has something to do with the fact that I keep copy paste copy paste etc.

thanks,

Adam

Posted by David on April 23, 2001 7:55 AM


What are you copying and pasting. A formula takes up more space than a value. you can always try to paste just the value with a paste special.

Posted by Cory on April 23, 2001 9:54 AM

Hi Dave, I saw your response and am curious as to why using the Workbook_Open in the "This Workbook" module is preferred over the Auto_Open procedure? Are there advantages to one over the other? Thanks. Tom

Posted by adam on April 23, 2001 12:16 PM

I need to copy formulas but why would something like this make my file go from 2 megs to 14. : I have a workbook that has macros that copy and paste many ranges. I just recently saved it and even though the file should be about the same size. the file went from 2 megs to over 14. can anyone explain why this happens and how to workaround this problem. I am assumming it has something to do with the fact that I keep copy paste copy paste etc. : thanks, : Adam


Posted by David on April 23, 2001 12:55 PM

It is hard to say without seeing the formula's and how much is being copied. If you are copying a lot of formulas then it will grow fast. If you think that it is an error I would be glad to take a look at your workbook. Just let me know.

Posted by Dave Hawley on April 24, 2001 12:17 AM


Hi Adam

Sounds like your file or Worksheet(s) have corrupted. See the below, this usually fixes the problem

1. Save the workbook as one version only. Don't save as multiple versions unless needed.

2. Export ALL modules and Userforms to your Hard drive.

3. Open a new Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then Copy the sheet to the new Workbook. Then save your new Workbook.

4. Do the same for all Worsheets, but each time you copy a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then try again.

5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.

6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.

Here are some intresting links. Some may apply ?

http://support.microsoft.com/support/kb/articles/Q186/3/69.ASP

http://archive.baarns.com/excel/develop/vbaperfm.asp

Dave
OzGrid Business Applications