+ Reply to Thread
Results 1 to 20 of 20

Move Entire Rows to Archive Worksheet

  1. #1
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    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
    Last edited by LBinGA; 12-30-2015 at 10:42 PM.

  2. #2
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move Entire Rows to Archive Worksheet

    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
    Last edited by LBinGA; 12-31-2015 at 10:12 AM.

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Move Entire Rows to Archive Worksheet

    Yes, please attach the workbook as it will make it easier for us to troubleshoot.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  4. #4
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Wink Re: Move Entire Rows to Archive Worksheet

    I think if I can get some code to strip the conditional formatting from the page I'm moving it from, on the way to the Archive
    page, it will solve the issue. Sheet attached.

    If I strip the Conditional Formatting manually from the Archive, the error disappears up until it reaches the limit again.

    Thank you,

    LBinGA

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Move Entire Rows to Archive Worksheet

    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.

  6. #6
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move Entire Rows to Archive Worksheet

    Right. If, when it craps out, you click Ok and then PASTE, it will reset itself and not bog down your system.

    I'm relatively sure at this point that it is the Conditional Formatting on the Archive page.

    Does anyone have some suggested vba for removing the formatting prior to it getting to the Archive tab?

    Thanks,

    LBinGA

  7. #7
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Move Entire Rows to Archive Worksheet

    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.

  8. #8
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move Entire Rows to Archive Worksheet

    Sorry Click End, Then Home & Paste & it will reset itself. I learned this the hard way with this error. I'm sorry it's doing it to you too.

    I think it works the first few times because once the Conditional Formats are cleared on the Archive page, it can "accept" a few more, then, when it reaches a limit, it gives the error.

    I tried adding the bolded line to my code, but it doesn't work:

    Please Login or Register  to view this content.
    Any suggestions?

  9. #9
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move Entire Rows to Archive Worksheet

    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
    Last edited by LBinGA; 12-31-2015 at 11:19 AM.

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Move Entire Rows to Archive Worksheet

    Try replacing the bolded line with
    Please Login or Register  to view this content.
    It worked for me several times without an error.

  11. #11
    Registered User
    Join Date
    08-09-2010
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move Entire Rows to Archive Worksheet

    THANK YOU. I tried that and was able to remove 25 rows over the 8 sheets to Archive without the error.

    Again, I'm sorry that it made your machine crap out.

    I believe it's been corrected so that I may place it back into the Sharepoint environment again. I've invested over 6 hours in trying to figure out the cause of this error. Who knew it might be the Conditional freaking Formatting?

    You were the only one who took the time to help me.

    Very grateful.....

    LB in GA

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Move Entire Rows to Archive Worksheet

    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.

  13. #13
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Move Entire Rows to Archive Worksheet

    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.
    Last edited by JapanDave; 12-31-2015 at 09:01 PM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Move Entire Rows to Archive Worksheet

    Posting garbage, to test for JapanDave...
    Please Login or Register  to view this content.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Move Entire Rows to Archive Worksheet

    Thanks Ford,

    But it won't let me post code.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Move Entire Rows to Archive Worksheet

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

  17. #17
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Move Entire Rows to Archive Worksheet

    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.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Move Entire Rows to Archive Worksheet

    woohoo that worked JD

    (was it the greater/less than?)

  19. #19
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Move Entire Rows to Archive Worksheet

    It was the less than. Thanks Ford.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Move Entire Rows to Archive Worksheet

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to cut/paste entire rows from one tab to Archive tab based on criteria in drop down
    By kmort in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-23-2015, 09:36 AM
  2. Trying to Move an entire Row from one sheet to another Archive sheet.. Also..
    By Gurbo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2014, 10:50 AM
  3. Replies: 1
    Last Post: 11-14-2014, 08:35 PM
  4. [SOLVED] How to move entire rows if a condition is met
    By mcculltc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2013, 03:46 PM
  5. Replies: 1
    Last Post: 09-14-2012, 09:02 PM
  6. Move entire row automatically to another worksheet if condition is met
    By starr5128 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2011, 06:38 PM
  7. How to archive an entire spreadsheet?
    By rarin in forum Excel General
    Replies: 3
    Last Post: 08-28-2007, 08:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1