Page 1 of 5 123 ... LastLast
Results 1 to 10 of 50

Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

This is a discussion on Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook within the Excel Questions forums, part of the Question Forums category; I have a Task List Workbook (with 2 Worksheets) Worksheet 1 will be just for Open Task Items. Worksheet 2 ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    2

    Default Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    I have a Task List Workbook (with 2 Worksheets)

    Worksheet 1 will be just for Open Task Items. Worksheet 2 will be just for Closed Items.

    Worksheet 1 will consist of rows of Open Items.

    The last column for each row on Worksheet 1 will either be a checkbox (for task completed) or a cell that we type a "completed date" into.

    Once the last column cell is checked as completed or the cell is populated with a complete date, is there a way to have that be the trigger for the entire row to transfer over (be cut) from Worksheet to Worksheet 2 of the same Workbook?

    Again, Worksheet 1 will be just for Open Task Items and Worksheet 2 will be just for Closed Items.

    Thank you for your help.

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    803

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    This caters for the use of dates to signal that an Open item is to be Closed (I don't know how to capture the Checkbox scenario)

    I have assumed that the sheets in your workbook are actually named "Worksheet 1" and "Worksheet 2" - otherwise you'll need to change the 3rd line of code below to suit.

    1. Assign a Defined Name to the entire last column, or just the range of cells, on Worksheet 1 in which completed dates are to be entered as "rngTrigger" (that's "r" "n" "g" ... not "m" "g")
    2. On Worksheet 2, select the entire row immediately under the last entry and assign the Defined Name "rngDest" to it (newly closed rows will be inserted above this location).
    3. Paste the following code into the Worksheet object for Worksheet 1 (not in a Module) in the Visual Basic Editor (select the tab for this sheet, then right click and select "View Code")
    Code:
     
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDest As Range
    Set rngDest = Worksheets("Worksheet 1").Range("rngDest")
     
    ' Limit the trap area to range of cells in which completed dates are entered as defined above
    If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then
     
    ' Only trigger if the value entred is a date or is recognizable as a valid date
         If IsDate(Target) Then
    'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
            Application.EnableEvents = False 
            Target.EntireRow.Select
            Selection.Cut
            rngDest.Insert Shift:=xlDown
            Selection.Delete
    ' Reset EnableEvents
            Application.EnableEvents = True
        End If
    End If
     
    End Sub
    Note that this will not work properly if you copy a completed date and simultaneously paste it to a range of cells in Worksheet 1, so you need to tag items in Worksheet 1 one at a time.

    Let me know how it goes.
    BigC
    Using Excel 2007 / 2010

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    2

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Is there a way for me to send you my Excel file so that you can set it up for me? Would you be willing to do that? Please advise.

    I don't feel confidant that I can follow the instructions correctly. And I am under a time crunch at this point.

    Joey Jay

  4. #4
    New Member
    Join Date
    Dec 2012
    Posts
    2

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Quote Originally Posted by BigC View Post
    This caters for the use of dates to signal that an Open item is to be Closed (I don't know how to capture the Checkbox scenario)

    I have assumed that the sheets in your workbook are actually named "Worksheet 1" and "Worksheet 2" - otherwise you'll need to change the 3rd line of code below to suit.

    1. Assign a Defined Name to the entire last column, or just the range of cells, on Worksheet 1 in which completed dates are to be entered as "rngTrigger" (that's "r" "n" "g" ... not "m" "g")
    2. On Worksheet 2, select the entire row immediately under the last entry and assign the Defined Name "rngDest" to it (newly closed rows will be inserted above this location).
    3. Paste the following code into the Worksheet object for Worksheet 1 (not in a Module) in the Visual Basic Editor (select the tab for this sheet, then right click and select "View Code")

    Code:
     
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDest As Range
    Set rngDest = Worksheets("Worksheet 1").Range("rngDest")
     
    ' Limit the trap area to range of cells in which completed dates are entered as defined above
    If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then
     
    ' Only trigger if the value entred is a date or is recognizable as a valid date
         If IsDate(Target) Then
    'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
            Application.EnableEvents = False 
            Target.EntireRow.Select
            Selection.Cut
            rngDest.Insert Shift:=xlDown
            Selection.Delete
    ' Reset EnableEvents
            Application.EnableEvents = True
        End If
    End If
     
    End Sub
    Note that this will not work properly if you copy a completed date and simultaneously paste it to a range of cells in Worksheet 1, so you need to tag items in Worksheet 1 one at a time.

    Let me know how it goes.



    BigC,

    I had a inquiry very similar to joeyjay's, and the information and code you provided above were great and did exactly what I wanted. The only thing I wanted to point out for others who try to use this is that I believe in the 3rd line of your code it needs to reference "Worksheet 2" and not "Worksheet 1." I received an error message originally but once I corrected that all worked as intended.

    Many thanks for the post and information!

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    803

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Yeah, well spotted. I think JoeyJay encountered that problem too with the code I posted, but then we were dealing offline from this forum. Below is the final code, which also uses the codenames for the sheets rather than the tab names which could be changed by the user and thereby cause the code to fail.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDest As Range
    Set rngDest = Sheet2.Range("rngDest")
    ' Limit the trap area to range of cells in which completed dates are entered as defined above
    If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
    ' Only trigger if the value entred is a date or is recognizable as a valid date
         If IsDate(Target) Then
    'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
            Application.EnableEvents = False
            Target.EntireRow.Select
            Selection.Cut
            rngDest.Insert Shift:=xlDown
            Selection.Delete
    ' Reset EnableEvents
            Application.EnableEvents = True
        End If
    End If
    End Sub
    Cheers
    BigC
    Using Excel 2007 / 2010

  6. #6
    New Member
    Join Date
    Dec 2012
    Posts
    2

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Quote Originally Posted by BigC View Post
    Yeah, well spotted. I think JoeyJay encountered that problem too with the code I posted, but then we were dealing offline from this forum. Below is the final code, which also uses the codenames for the sheets rather than the tab names which could be changed by the user and thereby cause the code to fail.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDest As Range
    Set rngDest = Sheet2.Range("rngDest")
    ' Limit the trap area to range of cells in which completed dates are entered as defined above
    If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
    ' Only trigger if the value entred is a date or is recognizable as a valid date
         If IsDate(Target) Then
    'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
            Application.EnableEvents = False
            Target.EntireRow.Select
            Selection.Cut
            rngDest.Insert Shift:=xlDown
            Selection.Delete
    ' Reset EnableEvents
            Application.EnableEvents = True
        End If
    End If
    End Sub
    Cheers

    Even better, this will be great to have/know too, thanks!

  7. #7
    New Member
    Join Date
    Apr 2013
    Posts
    41

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Can this code be modified, so instead of date some word can be used? For example if "good" is written, it triggers the event... ? Maybe it is simple task, but I'm still new and experimenting in VBA.

  8. #8
    New Member
    Join Date
    Apr 2013
    Posts
    41

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    I supposes this is it, in Line no.7 :
    If Target = "good" Then

  9. #9
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    803

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Yep - but to ensure the code works irrespective of which case is used (i.e. either "GOOD", "good" or "Good" - or any mix) it pays to convert the string entered to one case and then test that result, viz:

    Code:
    If Ucase(Target) = "GOOD" Then
    BigC
    Using Excel 2007 / 2010

  10. #10
    New Member
    Join Date
    Apr 2013
    Posts
    41

    Default Re: Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

    Quote Originally Posted by BigC View Post
    Yep - but to ensure the code works irrespective of which case is used (i.e. either "GOOD", "good" or "Good" - or any mix) it pays to convert the string entered to one case and then test that result, viz:

    Code:
    If Ucase(Target) = "GOOD" Then
    Great! Thank you BigC

Page 1 of 5 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com