Excel 2013 - Application.CutCopyMode locks / Excel crash - run time error 80010108 - copy failed

[TexasFM3]

Sorry about the subject, but I have no idea what to call this.

I am, however, really drawing a blank on this one.

I have a tool that has been created and runs fine in Excel 2007, but when I test it in 2013 I get a Excel Run-time error and am unable to debug because Excel crashes when told to do so.

Searching the net for possible causes there are numerous posts where folks are reporting the same issue and they all seem to stem from the use the following copy code from version 2007 to 2013. Also they are working with multiple workbooks.

        Sheets(OutName).Range("A2:R39").Select        
        ThisWorkbook.Sheets(OutName).Range("A2:R39").Copy
        ThisWorkbook.Sheets(OutName).Range("a" & RowCopy).Select
        ThisWorkbook.Sheets(OutName).Paste

Funny thing is there is no single reason for this to occur. The problem seems to manifests itself after the second running of the code. I have no idea why. There are a whole slew of potential fixes that resolve this problem for various people around the net, and after searching high and low I believe I finally hit upon the reason for my issue and it is due to the Application.CutCopyMode somehow getting stuck in Excel version 2013?

Reason I believe this is the cause is because after the first time the code runs, if I go to the workbook where the copy will occur should I run the code again and manually select the items that the code would copy and then try to do a ctrl-C of that selection the tool will crash the same exact way as running the code.

However, if I then restart the tool and run the code again and then before doing the manual copy step I did above I click the worksheet and select various cells and push the keyboard esc button, THEN select and ctrl copy. I am fine and I know the next go round of the code will work correctly.

I have tried programming this random selection of items to try and mimic my movements so that the CutCopyMode is totally squashed. I even set the mode = false but something is not the same and the code when run stand alone crashes again.

I know I have read various solutions on this site and what seems like a hundred more in other websites, but nothing seems to be getting me over this hump.

After reading all the random issues on the net I would think this is a MS issue, but nothing seems to be addressed or hot fixed.

Any and all help is much obliged.

Thanks,

FM3

 

[RoryA]

I'd suggest you try bypassing the clipboard using:

ThisWorkbook.Sheets(OutName).Range("A2:R39").Copy Destination:=ThisWorkbook.Sheets(OutName).Range("a" & RowCopy)

 

[TexasFM3]

Thanks for the insight RoryA...

It still fails - but at least it fails with a different description to the run time error 80010108 - now it states

Previously it would say Method 'Copy' of object 'Range' Failed

Any more insights is much obliged.

FM3

 

[RoryA]

Do you have filters set on either worksheet, or grouping/outlining, or do you have any activex controls on the sheets? (I've seen reports of all of these causing problems like that)

 

[TexasFM3]

Do you have filters set on either worksheet, or grouping/outlining, or do you have any activex controls on the sheets? (I've seen reports of all of these causing problems like that)

There are CMD Buttons on the page but these are not part of the copy.

My last resort is to try to close and reopen this workbook each time the code is run. It is a ugly work around, but maybe it will work.

There are so many things I have read. Items such as the selection that is copied cannot have formulas, or to turn off the calculation of the workbook while the copy is being done. I am at a blank as to what is going on. I do know that the code will make it each and every time on the first pass. However, on the 2nd pass when I regenerate the results then the run time error occurs.

I will report back if the open and close works - I suspect it will but one never knows until I try.

FM3

 

[RoryA]

Is the code run from the buttons? If so, does it work if you run it manually (either from the VBE or via the macros dialog) instead?

 

[TexasFM3]

Is the code run from the buttons? If so, does it work if you run it manually (either from the VBE or via the macros dialog) instead?

RoryA - the code is not run from the buttons of that workbook. The Tool as a whole actually has a Interface workbook that reads in two other workbooks. The interface workbook is then used to spawn off the other codes of the other two workbooks. The 3rd workbook read in is the one that is blowing up and its code set the cause of the Excel crash upon debug.

I know I said that I was able to run the code manually and believed I had identified the problem as the cutcopymode. However, the code now no longer seems to function. Now no matter what I try, the code seems to always crash and burn after the first successful completed run. Upon rerunning the "generate results" - the code will make it all the way up until this copy and then blow out. I have no reason or understanding why.

FM3

 

[mikepfly2]

RoryA - the code is not run from the buttons of that workbook. The Tool as a whole actually has a Interface workbook that reads in two other workbooks. The interface workbook is then used to spawn off the other codes of the other two workbooks. The 3rd workbook read in is the one that is blowing up and its code set the cause of the Excel crash upon debug.

I know I said that I was able to run the code manually and believed I had identified the problem as the cutcopymode. However, the code now no longer seems to function. Now no matter what I try, the code seems to always crash and burn after the first successful completed run. Upon rerunning the "generate results" - the code will make it all the way up until this copy and then blow out. I have no reason or understanding why.

FM3

I know this is an older thread but thought I'd add my 2 cents. I've experienced a similar problem and was bashing my head against the wall since my VBA ran fine in 2007 and 2010 but started freaking out in 2013. The culprit was with data validation in the cell range I was attempting to copy from one WB to another.

Out of curiosity, do any of the cells where you are trying to paste your data contain data validation (either a drop down type of list, or a message when a user enters a cell?)

I found that the destination cell (where I was going to paste the data) had data validation (found it using "Find & Select"-> "Goto Special" -> "Data Validation All" in the Editing section of the Home Ribbon menu). It was just a message that popped up when a user entered the cell. I removed the validation completely from that cell and everything worked fine. I don't know why it worked, just thankful it did.