Running VBA / Excel Crashes / Memory Errors

[tejay]

Hi All,

I am running the macros in the order shown below.

These macros effectively;

• Creates a spreadsheet
• Adds and downloads data
• Saves it
• Repeats the process

The first couple of times this works fine.

Than it just crashes with a variety of errors.

Each sheet that is created is only about 5mb in size.
The spreadsheet is currently 50mb in size.

About 60% of memory is used when VBA is running with about 70% of CPU.

Any ideas?

Thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[TMS]

I can't answer your question directly but a 50 Mb workbook is not doing anyone any favours.

You can maybe make the code a lot quicker and more efficient if you avoid selecting the worksheet(s) and range(s). For example:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

And this:

Please Login or Register  to view this content.

Regards, TMS

[tejay]

Hi TMS,

Thanks very much for your input.
I won't be able to verify if this has solved the problem until tomorrow.

With the second code;

Please Login or Register  to view this content.

This doesn't copy the cells down the range, do you know what I need to change?

Thanks

[TMS]

Probably just needs a dot (.) in front of Range("A2")

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Dohhh ... this works:

Please Login or Register  to view this content.

Regards, TMS

[tejay]

Hi TMS,

Thanks, made the changes.

Whilst the changes certainly made things more efficient, the excel spreadsheet would still crash when the file size got above 35mb or so.

I have decided to export each sheet as its own workbook and re-jig the sheet for that.

Somewhat pathetic excel cant handle 35mb + in this day and age, but anyway!

Thanks for your help

Tejay

[TMS]

You're welcome.

Each sheet that is created is only about 5mb in size.
The spreadsheet is currently 50mb in size.

To be fair, if I have a WORKBOOK that is 5mb, I'd think of it as big. I can't help wondering what you are downloading that takes up 5mb. That sounds to me as though you maybe you have a bloated template that you are copying, leading to 10 or more bloated worksheets. But, without being able to see the workbook, that's just a guess.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.