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

[joeyjay]

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.

 

[BigC]

Item 1:

I can't even get through your basic below information from Post #21 as I don't understand how to even define these ranges. 1.5hrs on and I am still scratching my head.

I can't even figure out why areas of a worksheet need to be defined when they are already named or referenced with Excel anyway (i.e. Column P which has the heading Complete).

If there is one thing to know/learn about Excel it's that there is more than one way to skin the cat! So the way most of the code posted in this thread works is due to the way I wrote it which is influenced by my preferences - but there are other ways to accomplish the same objective, particularly in regard to referencing ranges. I prefer to use Defined Names in the Excel interface (e.g. "rngTrigger" and "rngDest") rather than 'on-the-fly' referencing in the code such as that in your first post:

Const YesCol As String = "P"
Set Changed = Intersect(Target, Columns(YesCol))

Names merely provide a simple label by which to refer to a range or location in the workbook that saves having to use row and column referencing. They also provide the significant advantage in that referring to a Defined Name in VBA code ensures that it will always reference the correct underlying cell locations, for the "RefersTo" property of the Name is adjusted whenever rows/columns are inserted/deleted, whereas "hard" referencing to a cell/range (e.g. Sheet2!$A3:$B5) within VBA code does NOT get adjusted when changes in the sheet occur. Using Defined Names is just more dynamic/flexible, and requires less maintenance, if the structure of the underlying worksheet is likely to change.
Read about Defined Names (aka Named Ranges) in Excel's online help or via Google if you are unfamiliar with them - for they have many uses, in general spreadsheeting as well as in VBA. Then go to Chip Pearson's website (click here> Defined Names )and read about them both within Excel and VBA.

Item 2:

I did find this simple looking code,..

Private Sub Worksheet_Change(ByVal Target As Range) 
 If Target.Column = 5 Then
   If UCase(Target.Value) = "COMPLETE" Then 
     Target.EntireRow.Copy Destination:=Sheets("Archive"). _ 
     Range("A" & Rows.Count).End(xlUp).Offset(1) 
     Target.EntireRow.Delete 
   End If 
 End If 
End Sub

Why didn't you just use the "proven" code from this thread rather than re-inventing the wheel?
Having said that, your code above worked for me - without any change! What didn't work for you?
Possibilities:

1. I note that the above code refers to Column 5 (i.e. E) as the 'trigger' range into which "Complete" is entered, whereas your first lot of code referred to Column P (16)??
2. Alternatively, could the problem be due to the reason I discuss in the previous post from p9j123 (#29)? If so, follow my suggestion in that post and see if it helps. If not, come back with specific points as to what you did and what happened (or didn't happen when you were expecting something to occur, as the case may be.) and I'll see if I can help further.

 

[mariesnell]

Hi Big C,

Thanks again for your time in responding to my delima. I love the idea of a set range that encompasses all future changes so I will certainly read into these defined lists, I like the cleverness of it

The simple code I found as a 'quick fix' as I'm spending hours on this spreadsheet that I don't have but I don't see why it won't work for me. I'll have to look at it again when I have time but below is the basic code I tried to you -thank you for testing it to see if it work, at least I know it works for you! The code is against Sheet 1 (Active) and the only information on Sheet 2 (Archive) is the heading row so that each column has a title. But the code still won't work for me, not even the debugger is prompted, the code just doesn't activate.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Then
If UCase(Target.Value) = "Yes" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub

I'll re-read through all this post information one day again when I get the chance and hopefully get somewhere with it this time.

Thank you again for all your time!

 

[BigC]

Hi mariesnell

The debugger will only be activated if there is an error in the code, but if the code is not being fired in the first place, then obviously there's no way the debugger will activate.

1. First thing to ensure is that the Worksheet_Change code is behind the sheet object, rather than a standard module, in the VBE Project. Your comment that "The code is against Sheet 1 (Active)" seems to indicate that you have done that.
2. Secondly, make sure that the target area has been correctly referenced in the code. As mentioned in my last post, the code I tested (from post #30) references Column 5 (which is column E) as the 'trigger' range into which "Complete" will be entered, whereas your first lot of code (post #26, and your latest post #32) referred to Column P, which is column index #16??
3. Finally, as mentioned in my last post, one reason for applications not working when event procedures (e.g. Worksheet_Change) are utilised is that VBA's event handler has been deactivated (e.g. by some interruption to the code). To fix this, you need to MANUALLY reset the event handler. I have the following separate sub in a standard module that I run when I suspect this is the problem:

Sub ResetEnableEvents()
'   Resets Application.EnableEvents status to True when an event macro is interrupted.
'   Needs to be run manually

Application.EnableEvents = True

End Sub

HTH

 

[BigC]

Hi again p9j123

I found the workbooks I re-engineered for another Forum member a few months ago, and advise below what you need to do to have a separate workbook to which the "Completed" rows are "moved" rather than to a separate sheet within the source workbook.

1. Move your existing Sheet2 to a new workbook, and save that workbook as "Completed.xlsx". This will duplicate the existing Defined Name "rngDest" in the new workbook so you shouldn't need to re-create it.
2. However, to avoid any confusion, you do need to delete the original Defined Name "rngDest" remaining in Book1.xlsm
3. Your existing name "rngTrigger" in Book1.xlsm is required so leave that intact.
4. This is not necessary for the code to work, but if you wish to, rename the moved "Sheet2" in "Completed.xlsx" as "Sheet1"
5. In the original workbook ("Move to Completed sheet when completed.xlsm" - but I'll use "Book1.xlsm" for
   brevity):

• create a new Defined Name called "rngDestWbk" that RefersTo the new workbook (i.e. ="Completed.xlsx")
• copy and paste the following code into the sheet object of Sheet1 in the VBE project for Book1.xlsm
  

  Private Sub Worksheet_Change(ByVal Target As Range)
  
      Dim destWbk As String
      Dim wbk As Workbook
      Dim rngDest As Range
          
      'Get destination workbook name
      destWbk = ThisWorkbook.Names("rngDestWbk").RefersTo
      destWbk = Replace(destWbk, "=" & Chr(34), "")
      destWbk = Replace(destWbk, Chr(34), "")
      
      Set wbk = Application.Workbooks(destWbk)
      
      Set rngDest = wbk.Names("rngDest").RefersToRange
  
      'Limit the trap area to range of cells in which status selections are entered as defined above
      If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
          'Only trigger if the uppercase of value entered is "COMPLETED"
           If UCase(Target) = "COMPLETED" 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 solution is based on the assumption that you will have both workbooks open at the same time when tagging items as "Complete" - it does not include code to open the archive workbook if it is closed.

Post back with specific details if the above does not get you a working solution.

 

[todem12]

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.

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

Hello Big C,

I have followed this thread and it has worked for me. I use the earlier version with the date that triggers the row to move to another worksheet. It has worked to perfection. The question I have is: can you have multiple codes for worksheets in one workbook? I use this code for three different processes in my shop which means I have three separate workbooks. I would like to combine them into one workbook instead of opening three every day meaning an "orders" sheet and "completed" sheet for each process in one workbook. I have to keep them separate. I tried to put them in one as is but it didnt work. Im sure Im missing something in the code to change to recognize the individual sheets. I hope you can make sense of this. Can you help me? Thank you in advance of any help you can provide.

 

[BigC]

Hi todem12 & welcome to the Forum

This application is becoming so popular that I'm considering turning it into an Add-in and selling copies! (Just kidding - though I might reconsider that idea if the deployments reach 10.)

The question I have is: can you have multiple codes for worksheets in one workbook? I use this code for three different processes in my shop which means I have three separate workbooks. I would like to combine them into one workbook instead of opening three every day meaning an "orders" sheet and "completed" sheet for each process in one workbook. I have to keep them separate. I tried to put them in one as is but it didnt work.

Yes, this is certainly possible, and is similar to the solution worked out for user fatang1664 (posts #12-19) who needed to move rows between (i.e. back & forward) sheets according to a 'year' trigger, and then finally to "Lost" or "Completed" sheets when his projects reached the end of the line.

In a nutshell, you need code behind each sheet FROM which you wish to move rows to detect the input of the "trigger" word/date/value (which in your case is multiple triggers, and which are likely to be entered in different 'rngTrigger' ranges), and then move them to the desired destination/s. In your case, as with fatang1664 , you need the destination to change according to the trigger ("Order", "Complete", etc.), rather than being a single fixed destination sheet that is like a 'blackhole' where rows get moved but never get out again (as was the case for most other recipients of this solution).
The Named Ranges for both the trigger and the destination can be the same on each relevant sheet (in which case the Names need to be created with 'sheet' scope rather than 'workbook' scope) - these means the code is pretty much identical behind each sheet, with just the destination sheet name changing in the code that moves the target row. Alternatively, you can have a unique name for each destination range (e.g. rngDestOrder, rngDestComplete) and then the code only needs to reference the relevant Range according to the trigger entered.

If you have any difficulty getting your application to work, send me a Private Message through this Forum with your email address and I'll send you stuff that is a bit cumbersome to post into this thread - but please have a go at it yourself first, as this is how you learn.

HTH

 

[todem12]

Hi todem12 & welcome to the Forum

This application is becoming so popular that I'm considering turning it into an Add-in and selling copies! (Just kidding - though I might reconsider that idea if the deployments reach 10.)


Yes, this is certainly possible, and is similar to the solution worked out for user fatang1664 (posts #12-19) who needed to move rows between (i.e. back & forward) sheets according to a 'year' trigger, and then finally to "Lost" or "Completed" sheets when his projects reached the end of the line.

In a nutshell, you need code behind each sheet FROM which you wish to move rows to detect the input of the "trigger" word/date/value (which in your case is multiple triggers, and which are likely to be entered in different 'rngTrigger' ranges), and then move them to the desired destination/s. In your case, as with fatang1664 , you need the destination to change according to the trigger ("Order", "Complete", etc.), rather than being a single fixed destination sheet that is like a 'blackhole' where rows get moved but never get out again (as was the case for most other recipients of this solution).
The Named Ranges for both the trigger and the destination can be the same on each relevant sheet (in which case the Names need to be created with 'sheet' scope rather than 'workbook' scope) - these means the code is pretty much identical behind each sheet, with just the destination sheet name changing in the code that moves the target row. Alternatively, you can have a unique name for each destination range (e.g. rngDestOrder, rngDestComplete) and then the code only needs to reference the relevant Range according to the trigger entered.

If you have any difficulty getting your application to work, send me a Private Message through this Forum with your email address and I'll send you stuff that is a bit cumbersome to post into this thread - but please have a go at it yourself first, as this is how you learn.

HTH

Big C,

Thank you so much for your help. Your explanation worked great. Now I dont have to switch between workbooks but use just one for all three processes. I appreciate your time in explaining how to correctly code these worksheets.

Respectfully,

Todem12

 

[BigC]

Big C,

Thank you so much for your help. Your explanation worked great. Now I dont have to switch between workbooks but use just one for all three processes. I appreciate your time in explaining how to correctly code these worksheets.

Respectfully,

Todem12

Glad to hear that you got it working, and thanks for the positive feedback.

Cheers

 

[todem12]

Hello Big C,

Can this code be used to COPY a row from one worksheet to another using a company name as a trigger? I would like to copy completed rows from one worksheet to other worksheets from each company name. Based on the posts #7-#10, you can change line #7 in the code to accommodate a word instead of a date but I see that line #4 and #6 also have "date" written in them. Would they have to be changed too? Can I get the rows to copy to their respective sheets based on the name in a column? I appreciate any further help from you. Im fascinated by this coding and would love to delve into it further. I thank you in advance.

 

[BigC]

Hi again todem12

Here is an adaptation of the code I developed for fatang1664 who wanted rows to move between sheets (with year numbers as sheet names) based on a year value entered in the trigger range. You'll need your sheet names to match the acceptable values for the trigger range on each sheet (to which needs to be assigned the Defined Name "Company" with sheet scope). Apply Data Validation to "Company" that refers to a list of company names (in a range with Defined Name "Company_DVOptions") .

Each sheet needs the following code behind it:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strSourceWks As Worksheet
    Dim strDestWks As Worksheet
    Dim rngCoy As Range
    Dim rngDest As Range
    Dim strCoy As String
    Dim rngFoundCell As Range
      
    Set strSourceWks = ActiveSheet
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Set rngCoy = strSourceWks.Range("Company")

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then GoTo Finish
      
        strCoy = Target.Value
        
        ' Check to see if a the company name entered matches the allowable values (i.e. a sheet with that name exists)
        With Sheet1.Range("Company_DVOptions")
            Set rngFoundCell = .Find(What:=strCoy, LookIn:=xlValues)
        ' If company name is out of range or same as sheet updated then exit
            If rngFoundCell Is Nothing Or strCoy = strSourceWks.Name Then GoTo Finish
        End With
        
        If strCoy <> strSourceWks.Name Then
            
            Set rngDest = Worksheets(strCoy).Range("Destn")
        
            Target.EntireRow.Select
            Selection.Copy rngDest
            Selection.Delete
                        
        End If
           
        rngDest.Parent.Activate
        rngDest.Select

Finish:

    ' Tidy-up
        Application.EnableEvents = True
        Application.DisplayAlerts = True
        
End Sub