Excel Crashing without Error when running macro

Hi there,

I have created a Macro that does the following:
- Takes 20 cells (which contain headings) and stores them in an Array
- Looks into a network folder and lists 50 Excel files into an Array
- It then loops through each file, while in each file is finds each column heading and copies a set number of records to a new sheet.
- Once all 20 headings have been copied (and data below them) it goes to the next file until all 50 files are complete

After every time I copy, I reset the Clipboard.

Sometimes half way through the macro, it simply crashes with no error report. Sometimes it completes without any issues. I can adjust the columns it copies and when I do less it fails less and sometimes never. The more columns I add the more unstable it gets.

Is there a stack that's getting to big that I can reset? Obviously doing one sheet is no problem with lot's of rows...so if there was a way to break and reset I feel I would eliminate Excel crashing.

I have a variable that increments by the amount of copied rows (so it knows where to paste in the new sheet) that is a running total. So I can simply reset the whole macro every book very easily.

Thanks,
Robb

Absolutely, I probably should have done that right away. I am no expert but I am learning so don't be too critical.

Please Login or Register  to view this content.