Move Entire Rows to Archive Worksheet

I have a spreadsheet that has been working perfectly for months. Now, intermittently, when I attempt to move of a row (by entering "Yes" in the column), I get this error
Run-time error'-2147417848 (80010108)':
Method 'Insert' of object 'Range' failed

I have 9 sheets. All sheets have the following code onto the Archive Tab:

Please Login or Register  to view this content.

The code stops on the bolded line above, then in the bottom left hand corner of the screen it says "Select Destination and click enter or choose Paste".

If I go nowhere except to the top Menu and click Paste, Excel craps out, recovers and when it comes back, the Row has been moved to the Archive.

I checked the code over and over. There isn't a clear reason why it should work for months and now doesn't....or that it should work 4 or 5 times, different sheets, different rows, then gives the error.

Additionally, I've checked the rngDest Named Range. It correctly shows the next blank row on the page as =ARCHIVE!$59:$59, and increased with each add, correctly. Everything appears to be correct...and has been working. Until today.

Could my code to sort have anything to do with it?

I can upload the sheet if someone would like to test it.
ANY help would be much appreciated. Upper management uses this sheet and I would like to get it working before it happens to one of them.

Thanks so much,
LBinGA

I believe I discovered the reason for the error. It is copying the Conditional Formatting in the Row over to the Archive page and it will only handle that so many times on that page. Can anyone help me with a code that would strip the Conditional formatting from the row as it gets entered onto the Archive tab? Again, will be happy to post my spreadsheet if anyone cares to test it out.

Thanks in advance,
LBinGA

Yes, please attach the workbook as it will make it easier for us to troubleshoot.

Well, I gave it a shot. It works for a few times when you type yes in the completed? column, but after a few times I get the same error and it completely bogs down my system so I'm unable to figure out what's causing the problem.

I get a debug window with the error and my only options are End or Debug (no OK). Clicking Debug bogs down the system and I have to go through task manager to close excel. Clicking End then paste causes excel to crash.
Its kind of odd how it works great the first few times and then errors out.

Ok, I replaced the above bolded line with:

Please Login or Register  to view this content.

It's working by cutting and removing the formats but now when I look at the Archive page, there is nothing there...rngDest line moves down but the lines above it are blank.

Ugh. I don't want it to remove the actual TEXT. Thoughts?

LB in GA

Try replacing the bolded line with

Please Login or Register  to view this content.

It worked for me several times without an error.

Not a problem. I'm glad I was able to help out....and I learned something along the way.
As for the "only one".... It could be because others saw that I was helping you and they were just giving us the chance to get it figured out.

Type "yes" until your heart is content.

You need to get rid of that error handling and deal with it directly if possible. So I took out the "On Error Resume Next" line and replaced it so you don't run through unnecessary code.

Please Login or Register  to view this content.

And yes, most times if someone has already replied most people leave it to them.

Posting garbage, to test for JapanDave...

Please Login or Register  to view this content.

Thanks Ford,

But it won't let me post code.

JD did you see my comment in your other thread (where you said you couldnt post here)?

OK, Here goes, hopefully I can post the correct code this time.

Please Login or Register  to view this content.

Now take out the spaces on either side the"<" on the line below when using the code.

Please Login or Register  to view this content.

woohoo that worked JD

(was it the greater/less than?)

It was the less than. Thanks Ford.

Thanks for the update, I will pass that onto the TT (when I get home)