+ Reply to Thread
Results 1 to 6 of 6

Macro to copy a line to archive sheet and delete from active sheet

  1. #1
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    194

    Macro to copy a line to archive sheet and delete from active sheet

    My attached spread sheet has a macro that copies the line highlighted to the archive sheet and then deletes that line from the active sheet the macro is activated by hitting the button at the top left of any of the department sheets.
    The macro worked fine until the end of our fiscal year when I removed all the archived work orders from the archive page, to start the year new.
    Now when I try to archive something from one of the department sheets, it copies it to archive and gives me a range error.
    Up until now there has always been information on the archive sheet, I'm not sure if removing all the information on that sheet caused the macro to stop working properly.

    Could someone please look at the macro set up and tell me if I am doing something wrong?

    I'd be happy to send the last working file upon request. (the file that has all the information in the archive section and was running the macro correctly)

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Macro to copy a line to archive sheet and delete from active sheet

    After some test, I can't reproduce the error you are talking about.
    The way the macro is programmed, I can't see how you could get a range error.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Macro to copy a line to archive sheet and delete from active sheet

    were you able to move the line to archive and have it erase the line on the active sheet?

  4. #4
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Macro to copy a line to archive sheet and delete from active sheet

    in sheet 'Dept 5811' I highlighted row 87 then hit the macro button on the top left of the sheet.
    The program stopped, when I hit debug it said "run time error, Delete method of range failed."

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Macro to copy a line to archive sheet and delete from active sheet

    I did the exact same thing as you mentioned and no error occurs.
    The line where the cursor is located is transfered to Archive and then deleted from that sheet.
    It works if you select the entire row or any cell on that row.

  6. #6
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Macro to copy a line to archive sheet and delete from active sheet

    I selected the entire row, as soon as I hit the button, I get the debug box, the program loops until I hit escape, then I'm able to go into debug.
    I've had the same result on my work computer and my home computer.

+ 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. Macro to select sheet and copy data from one sheet to another on new line every time
    By goatie43 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2015, 07:26 AM
  2. [SOLVED] VBA - Macro issue copy/paste line other sheet + duplicate current sheet
    By vcourbiere in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-17-2014, 12:06 PM
  3. [SOLVED] Need a macro to copy dates & data from one sheet to an updating archive sheet
    By pbj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2013, 06:58 PM
  4. Replies: 1
    Last Post: 01-07-2013, 12:23 AM
  5. Delete Rows that contains zero and Copy active sheet to New Sheet
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2009, 02:02 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