Excel cannot complete this task with available resources.

**Sean Thomas** · 12-03-2012, 01:12 PM

Hi,
Can anyone give me some advise on this?
The project i was working on yesterday was all running fine. I have not made any changes to the code and ran it today and recieve this message.

Excel cannot complete this task with available resources. Choose less data or close other applications.

Basically the code i am running filters data from a main data sheet. It then copies certain data to another sheet. It then copies further data a differnet sheet.
It then creates 2 new workbooks with a copy of each one of these sheets with data on, in each sheet.
Opens Outlook mail, attaches 2 new workbooks, then kills the 2 new workbooks.

I have found it only seems to fail on the 2nd workbook. It copies the data to the sheet, creates a new workbook and then fails. It does not allow me to run through the code to find the problem.

I think it may be because the sheet it is copying has a lot of data validation dropdown boxes and possibly causing it to crash due to too many calculations maybe? The new workbook when it was working was only about 370kb.

Im sure its either the code or too much information to copy.

Any ideas?

Here is the code i am using.

Please Login or Register  to view this content.

**Winon** · 12-03-2012, 01:23 PM

Hello Sean Thomas,

Have you tried to set "Application.Calculation" to manual at the beginning of your Code, and then at the end, back to "Automatic"?

**Sean Thomas** · 12-03-2012, 01:28 PM

Just tried that and it made no difference.
Thanks anyway

**Sean Thomas** · 12-03-2012, 05:25 PM

Anyone else got any ideas please?

**Winon** · 12-04-2012, 01:42 AM

The new workbook when it was working was only about 370kb.

What is the size of the WorkBook now? If it is much larger, highlight the first empty column or two to the far right of your used range/s and delete it. Repeat the process by highlighting the first couple of Rows below your used range/s and delete that as well. Then Save the WorkBook and check if the size is back to "normal".

If that does not help. repeat the processes above and save it with a new name. Again, check if the size is back to "normal".

If this does not solve the problem, try closing as many other applications that may be running and are not needed for your Project.

Should that also fail, try saving your Project where the Code creates:

Please Login or Register  to view this content.

Hope that helps.

**Sean Thomas** · 12-04-2012, 03:35 AM

Thanks Wilson for your advice.

However i have fixed it.
my original workbook is a .xlsm of 2MB.
The original sheet was a copy from a .xls doc. When i have copied it into my workbook, the sheets are much larger. I also noticed the original doc had data validation cells and these have been copied from top to bottom of the new doc. It also had all the list details for data validation at the very bottom. Therefore when copying the sheet, it was taking a lot of memory to copy the whole doc.

I have now reduced the doc in size, deleted the data validation items not required (only the 1st 400 rows are ever likely to be used). Ive also moved the data validation lists to the top right.

This now works fine.

probably the combination of a 2MB original doc, opening outlook and creating 2 new workbooks was too much, what with the whole of the 2nd workbook to check when copying.
Thanks anyway.