+ Reply to Thread
Results 1 to 8 of 8

Move a row from one worksheet to another when a date is entered in the Date Claimed column

Hybrid View

  1. #1
    Registered User
    Join Date
    06-25-2014
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    17

    Move a row from one worksheet to another when a date is entered in the Date Claimed column

    I have a lost and found log that has 2 worksheets: (1) Unresolved and (2) Resolved. Data is entered into the Unresolved worksheet when something is lost or found. When a date is entered under Date Claimed / Sent to Capitol Police (column I), I would like the data in that row to be removed from the Unresolved worksheet and automatically inserted in the next blank row of the Resolved worksheet. I know very little VB, so any help is greatly appreciated. Thank you

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    Well, the first thing you should know about VB is that is pretty spreadsheet specific, so we'll be able to little without an attachment provided.
    I'd recommend scrubbing your data and providing at least a rough outline.

    That said, you'll need to utilize a worksheet change event to continuously run through sheet1 every time something changes. When it does, it'll copy the relevant data. We toss in a filter to re-sort page 1 to fill in the gaps after the cut & paste.

    This code goes directly on the worksheet instead of in a module.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 9 And Target.Row > 1 Then
        Target.EntireRow.Cut Sheets("Found").Range("A" & Rows.Count).End(xlUp).Offset(1)
            With Worksheets("Lost").Sort
                .SetRange Range("A2:I99")
                .Orientation = xlTopToBottom
                .Apply
            End With
    End If
    
    End Sub
    Attached Files Attached Files
    Last edited by daffodil11; 06-25-2014 at 06:51 PM. Reason: Included attachment
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    06-25-2014
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    17

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    Here is my form. Will this help?
    Attached Files Attached Files

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    Sure. We just make a few small changes. We move the targeting to > 5, change the method to copy and paste to preserve formatting, and adjust the range of the re-sorting.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 9 And Target.Row > 5 Then
        Target.EntireRow.Copy Sheets("Resolved").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.ClearContents
            With Worksheets("Unresolved").Sort
                .SetRange Range("A6:I1900")
                .Orientation = xlTopToBottom
                .Apply
            End With
    End If
    
    End Sub
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-25-2014
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    17

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    I tried filling in dates for several random rows, but it only copied over two rows.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    It's because you're typing on random lines with no data. It's copying each record directly below the last entry in Column A of Resolved.

    If there's no data in A on the resolved then it's put the next record directly on top of it. Put something in A of unresolved first, then add a date in I.

  7. #7
    Registered User
    Join Date
    06-25-2014
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    17

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    Perfect! It seems to work good. I will have a coworker test it out more extensively, but it seems to be working. Thanks so much!

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Move a row from one worksheet to another when a date is entered in the Date Claimed co

    Glad I could help out.

+ 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 move cursor to column containing current date at worksheet activation
    By Snoddas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-03-2013, 05:40 AM
  2. Replies: 1
    Last Post: 08-07-2013, 11:09 PM
  3. Need to move row to a new worksheet based on the date in a particular column
    By MDNerey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2013, 05:51 PM
  4. 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
  5. Replies: 8
    Last Post: 08-06-2012, 10:12 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