Excel crashing due to loop

[MarkCBB]

HI there,

I am running some code that very basically copies and moves a sheet from a workbook and saves it, every loop creates an invoice, currently it loops 170 times but excel cant seem to make it past 170 all the time. is there any reason why this happens?

 

[Ruddles]

There's nowt inherently significant about that number like there is about 255, 32767, 1048576, etc.

I'm assuming you don't have an On Error directive active in your code at the point at which the loop terminates.

Have you tried stepping through your code to find out what's triggering the condition which causes your loop to terminate?

 

[moonfish]

What happens if you start your loop at, say, the 150th invoice? Does it still stop after 170 invoices, or does it stop after 20?

 

[pcc]

Rather than moving the sheet, you could try:

add a new workbook
activate the source workbook
copy the cells (or just the used range)
activate the new workbook
paste the data (or paste values, format, whatever you want
(clear the clipborad afterwards)
rename the sheet if necessary
delete sheet from source workbook

I have had similar probelms in the past and this worked for me

The end result is the same
Cheers

 

[MarkCBB]

HI there Guys,

I have step though it (takes along time to do) when I step though it it seems to work. however when i run the code it sometimes works and sometimes "excel is not responding, once that happens the dugbug box appears, however there is not bug. the code is very simple.

the strange thing is that sometimes it will run for all 170, and somtimes it doesnt, maybe excel is casheing the copied worksheets?

 

[MarkCBB]

just happened again. arg. "excel has stopped working" check online for solution

 

[moonfish]

This might be a weird suggestion but you could try saving the file after every 100 processed invoices (possibly to a temporary location). This could clear Excel's cache, or at least parts of it.

 

[pcc]

Did you try my suggestion?

 

[MarkCBB]

@pcc, hey that sounds like an idea, how would i clear the clipboard?

@Moonfish, I cant save after 100 as they are all individual invoices for separate companies.

 

[pcc]

Application.CutCopyMode = False