+ Reply to Thread
Results 1 to 4 of 4

Macro to Archive (cut and paste) data from one worksheet to another

  1. #1
    Registered User
    Join Date
    09-27-2017
    Location
    CA
    MS-Off Ver
    2013
    Posts
    2

    Macro to Archive (cut and paste) data from one worksheet to another

    Hello,

    I am hopeful someone can help me with creating a macro that would allow me to do the following:

    I have two worksheets within the same Excel workbook: 'Report Tracking' and 'Archive.' Both worksheets have headings that are identical and encompass rows 1 & 2 and contain some merged cells. Therefore data starts in row 3 and is contained between columns A and AG. Column AH is labeled as "Complete?". I would like for when I type "Yes" into AH to trigger all the data from that row (not all cells in the row will contain data) to be copied from 'Report Tracking,' pasted into 'Archive,' then deleted from 'Report Tracking.' I assume the remaining rows in 'Report Tracking' would be bumped up once a row has been marked complete and moved. There will be many entries in 'Report Tracking' at any one time and they won't necessarily be marked complete in order.

    Thank you in advance!

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Macro to Archive (cut and paste) data from one worksheet to another

    Hello ExcellentAmanda,

    Try the following code in the "Report Tracking" sheet module:-
    Please Login or Register  to view this content.
    I've used the sheet codes in the above macro (Sheet1 and Sheet2) so I'm assuming that "Report Tracking" is Sheet1 and "Archive" is Sheet2.
    Once you enter "Yes" in any cell in Column AH and click away (or press enter or down arrow), the relevant row of data will be transferred to Sheet2 (Archive). The row will then be deleted from the Report Tracking sheet.

    With this code, the criteria "Yes" is case insensitive.

    To implement the code:-

    - Right click on the "Report Tracking" sheet tab.
    - Select "View Code" from the menu that appears.
    - In the big white field that then appears, paste the above code.

    I hope that this helps.

    Cheerio,
    vcoolio.

  3. #3
    Registered User
    Join Date
    09-27-2017
    Location
    CA
    MS-Off Ver
    2013
    Posts
    2

    Re: Macro to Archive (cut and paste) data from one worksheet to another

    Thank you for your quick reply.

    I did realize my "Archive" was on Sheet3 (Sheet2 must have been deleted in the process of creating the spreadsheet). I updated the code you provided from Sheet2 to Sheet3.

    Then, when I entered "yes", the entire row disappeared from Report Tracking(Sheet1), but when it was moved to Archive(Sheet3), Columns G through AF were the only columns that copied over, and they replaced the heading in those columns. I think it could be related to the formatting of my two header rows-- as I said, some cells were merged, and some not.

    When I re-did my header on the Archive sheet as one row only, no wonky merged formatting, it copied over nicely! Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Macro to Archive (cut and paste) data from one worksheet to another

    Hi ExcellentAmanda,

    You're welcome. Glad that I was able to help.

    Yes, merged cells play havoc with macros and are a thorn in the side of programmers. However, its good to see that you took the initiative and consigned them to Merged Cell Hell!

    Do we assume that your issue has now been resolved?

    Cheerio,
    vcoolio.

+ 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] Archive Macro - Student Data
    By KristofferA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2017, 07:28 AM
  2. Excel VBA Archive Monthly Account Returns to an Archive File
    By cmarenco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2016, 10:12 PM
  3. [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
  4. Macro to copy data from one worksheet and paste to another worksheet
    By robertmiller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 09:56 AM
  5. Macro to retrive data for a quote that is in archive
    By marianov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2012, 08:41 AM

Tags for this Thread

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