+ Reply to Thread
Results 1 to 14 of 14

Transfering a row of data from one sheet to another when a date is entered in a column

  1. #1
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Transfering a row of data from one sheet to another when a date is entered in a column

    Hey smart folk...

    I am working w/ an excel file that has multiple sheets that I want to program automatic functions into. One is "Waitlist" which is the primary sheet where data is entered. Once a date is entered into column Q "assigned date," I would like that entire row to be transferred (moved entirely, not just duplicated) into the first available row in a second sheet "Assigned." Currently data is being cut and pasted manually, which has led to multiple user initiated errors. I am unfamiliar w/ coding, so any info would help! I found something online before, but it kept bringing up an error that I couldn't figure out. (Naturally, I can no longer find this old thread, so I can't even show it as an example.)

    Mahalo in advance for any and all of your help!
    Lei

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Put this in the worksheet change event for the Waitlist Sheet. Save the VBA. Save the file as an .xlsm file.

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Try this one.
    If you have a new excel then as above you can save the file ".xls" as an ".xlsm" file.
    The macro responds only to the date entered in column Q.
    Attached Files Attached Files
    Last edited by mjr veverka; 10-11-2017 at 05:47 PM.

  4. #4
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Hey Alan,

    Thanks. That has been the closest solution so far! Any way to ensure the original row in "Waitlist" is deleted once transferred?

    Mahalo,
    Lei

  5. #5
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Hey Porucha,

    Unfortunately, I am unable to open your excel on my computer. Any chance you can just include the code and any additional info?

    Mahalo,
    Lei

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Try it this way.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Hey Bakerman2,

    That totally worked! Thanks!!! I have the sheets formulated as tables, but the data is being entered in the row below the table, so not formatting as a table. Is it possible to have it formulated like the table (ie. alternating colors of each row) or should I just be grateful it worked at all? haha

    Mahalo,
    Lei

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Table just expands here.
    Otherwise post example file.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Quote Originally Posted by LeimomiD View Post
    Unfortunately, I am unable to open your excel on my computer. Any chance you can just include the code and any additional info?
    Vba code:
    Please Login or Register  to view this content.
    You have to paste it to Sheet1 module in Editor vba (Alt + F11).

    However, due to the absence of exception handling, macro will copy empty lines if you enter dates in a cells with an empty rows.
    Attached Images Attached Images
    Last edited by mjr veverka; 10-12-2017 at 09:24 PM.

  10. #10
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    I appreciate all the assistance! Will likely reach out to this forum again for the next question as it arises...

    MAHALO!

  11. #11
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    You're welcome and thanks for rep+.

  12. #12
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Ok at this again, but just copying the macros hasn't worked, so needing help re-writing them!

    1. I have a table in excel and I would like the entire row of data to transfer from Sheet 1 to Sheet 2 ONLY when a date is added to Sheet 1, Column L.
    2. Both Sheet 1 and Sheet 2 are formatted as tables, so when the row of data transfers, I would like it to land within the table, but not overwrite any existing data on Sheet 2.
    3. Once the row of data is successfully transferred from Sheet 1 to Sheet 2, I would like it to automatically delete from Sheet 1. (Total transfer of data, not duplication of data.)

    Mahalo in advance for your help!

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    10-06-2017
    Location
    Honolulu, Hawaii
    MS-Off Ver
    2013
    Posts
    7

    Re: Transfering a row of data from one sheet to another when a date is entered in a column

    Nevermind - finally got it to work! Mahalo for your help!

+ 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. Transfering data from one main sheet to multiple worksheets via date and status
    By SBlack6 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-17-2017, 12:40 PM
  2. [SOLVED] if data is entered in column A, column B should have date automatically entered.
    By exceltriumph in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-30-2016, 12:02 PM
  3. Moving complete rows of data from one sheet to a new sheet when a date is entered
    By meadnl89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2014, 11:39 AM
  4. MOVE all values in a row to next sheet when date is entered in column
    By IreneADS in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2014, 01:39 PM
  5. Move entire row of data from Sheet 1 to sheet 2 when completed date is entered.
    By jt7721 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2013, 11:50 AM
  6. Move row of data to another sheet once a date is entered in a specific column
    By stankanie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2012, 09:21 AM
  7. [SOLVED] Summation of amounts by date from one sheet and transfering onto a summary sheet
    By hasaneen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-19-2012, 10:57 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